How to loop in Select Query SQL

Question

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

1. SQL is a set-based language and not a procedural language. The concept of a "Loop" is meaningless to it. You should instead ask how you can build sets and Union and Intersect them to get your result. 2. Your example seems a little off to me. The last record of your example result is outside the contract period for ICN A, and you did not include 12/31/2019 which is within it. You might want to express your month rules verbally before trying to write them into code. — Chris Maurer
– Chris Maurer, Commented Jun 17, 2021 at 4:17
@ChrisMaurer, hi sorry yeah you right. the output should start from the 2019 date. so I should create GroupBy but using union or intersect how to make the result row total based on the period. the amount is from contract value divided by period — Theodorus Agum Gumilang
– Theodorus Agum Gumilang, Commented Jun 17, 2021 at 4:29
Iterations (loops) are done with recursive queries in SQL. learn.microsoft.com/en-us/sql/t-sql/queries/… — Thorsten Kettner
– Thorsten Kettner, Commented Jun 17, 2021 at 5:48
So the dates in your results mark the starting day of a month-like sub period? 2019-12-31 means the sub period starts on 2019-12-31 and ends on 2020-01-30 (month number increased by one, day number decresed by one) or the contract's end in case it occurred before 2019-12-31. So for contract A the last date is 2020-02-29 (because 2020-02-31 and 2020-02-30 don't exist), which stands for the sub period 2020-02-29 to ... erm ... 2020-03-28 or 2020-03-29 or 2020-03-30? Probably the last option, because otherwise you'd miss a row starting with 2020-03-29 or 2020-03-30. — Thorsten Kettner
– Thorsten Kettner, Commented Jun 17, 2021 at 6:55
For contract D the last date will be 2021-05-27 standing for a period of four days from 2021-05-27 to 2021-05-30. Yes? Shouldn't the amount shown differ depending on the number of days in the period? You are showing 6,515,398 regardless of the period's length. — Thorsten Kettner
– Thorsten Kettner, Commented Jun 17, 2021 at 6:55

hsn · Accepted Answer · 2021-06-17 06:44:43Z

1

;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
...

edited Jun 17, 2021 at 6:44

answered Jun 17, 2021 at 5:49

hsn

3682 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Thorsten Kettner Over a year ago

@Theodorus Agum Gumilang: I suggest you look at recursive queries in order to write a better one them the one suggested here. Wile hsn's query works for contracts of up to 100 months, it seems weird to generate numbers 0 to 99 to iterate through the rows. You should instead simply iterate from startcontract to endcontract directly. No need for a set of numbers, no need for CROSS APPLY, just one recursive CTE.

Theodorus Agum Gumilang Over a year ago

Hi @ThorstenKettner, thanks for your suggestion I will look into your given documentation above. Maybe the iterate can be done from Periods number.

Charlieface Over a year ago

@ThorstenKettner You don't even need a recursive CTE, you can use Itzik Ben-Gan's famous tally function: a series of cross-joins against VALUES(1),(1),(1)... followed by a ROW_NUMBER calculation

Thorsten Kettner Over a year ago

@Theodorus Agum Gumilang: As mentioned in the request comments, the periods number should not exist in a normalized database (or be a virtual computed column only). You need startcontract and endcontract and loop from startcontract until you reach endcontract. That's all.

Collectives™ on Stack Overflow

How to loop in Select Query SQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related