Select column from table number of times by defined condition

user_14184798 0
Viewed 34

Need to select record number of times by condition so each ID needs to be selected number of qty time

Example:

ID |qty |column_A
1   13   12/31/2020
2   25   1/1/2021
3   34   1/2/2021
4   198  1/3/2021
5   97   1/4/2021

Output expected:

1
1
1
1
1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
2
2
2
2
2
...

Note:

qty=DATEDIFF(MONTH,'date',z.column A)+1
recursive-query subquery union sql-server sql
1 Answers

One option uses a recursive query. In standard SQL, you would phrase this as:

with recursive cte (id, qty) (
    select id, qty from mytable
    union all
    select id, qty - 1 from cte where qty > 0
)
select id from cte order by id
user_106767160
Related