CODEWITHSUNDEEP
sqlsql-server
Theodorus Agum Gumilang
Theodorus Agum Gumilang

Reputation: 1486

How to loop in Select Query SQL

Hi I have this Table like this

ICN         StartContract           EndContract             ContractValue   PeriodOfContract
A           2019-12-31 17:00:00.000  2020-03-30 17:00:00.000    19546194.00     3.00
B           2019-12-31 17:00:00.000  2020-12-30 17:00:00.000    1397095800.00   12.00
C           2021-02-28 17:00:00.000  2022-02-27 17:00:00.000    4016244584.00   12.00
D           2018-05-27 17:00:00.000  2021-05-30 17:00:00.000    9686992857.00   36.00

I want to create a view to loop a Period of Contract and create a new row in My View. Example Result

ICN Date        Amount
A   2019-12-31  6,515,398
A   2020-01-31  6,515,398
A   2020-02-29  6,515,398

I've seen some loop examples but mostly it's used only on the function. I want to loop the period of the contract and populate a new row based on the period on my View. This is My Query

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT
ICN, StartContract , EndContract, ContractValue, PeriodOfContract
  FROM [FactProject]

Any suggestion or example query to do that? Thanks

Upvotes: 1

Views: 234

Answers (1)

hsn
hsn

Reputation: 368

;WITH n(n) AS
(
    SELECT 0
    UNION ALL
    SELECT n+1 FROM n WHERE n+1 < 100
)
SELECT ICN, C.[Date], C.Amount FROM T
CROSS APPLY
(
    SELECT 
        CAST(DATEADD(MONTH, n, StartContract) AS date) [Date],
        CAST(ROUND(ContractValue / PeriodOfContract, 0) AS int) Amount  
    FROM n 
    WHERE
        n.n < T.PeriodOfContract
) C
ORDER BY ICN, [Date]

I used WITH n(n) to generate a list of int numbers (explained here). Note: You should increase the number 100 if you may have a PeriodOfContract value more than 100.

Output:

ICN Date         Amount
A   2019-12-31   6515398
A   2020-01-31   6515398
A   2020-02-29   6515398
B   2019-12-31   116424650
B   2020-01-31   116424650
...

Upvotes: 1

Related Questions