we have an old legacy table with our point system that looks like this:
| from date | until date | points monday | points tuesday | points wednesday | points thursday | points friday |
|---|---|---|---|---|---|---|
| 6-12-2021 | 10-12-2021 | 10 | 30 | 20 | 15 | 5 |
| 13-12-2021 | 13-12-2021 | 10 | 0 | 0 | 0 | 0 |
now for power bi and our analytics, we want to create a select query that makes an result like this:
| date | points |
|---|---|
| 6-12-2021 | 10 |
| 7-12-2021 | 30 |
| 8-12-2021 | 20 |
| 9-12-2021 | 15 |
| 10-12-2021 | 5 |
| 13-12-2021 | 10 |
how can something like this be accomplished in db2?
thanks in advance for the help!
CodePudding user response：
you can list all dates with a recursive CTE and then DECODE the result of DAYOFWEEK_ISO to set points
with table1 (fromdate, untildate, monday, tuesday, wednesday, thursday, friday) as (
values
(date '2021-12-06', date '2021-12-10', 10, 30, 20, 15, 5),
(date '2021-12-13', date '2021-12-13', 10, 0, 0, 0, 0)
),
alldates (fromdate, untildate, monday, tuesday, wednesday, thursday, friday, points_date) as (
select table1.*, fromdate as points_date from table1
union all
select fromdate, untildate, monday, tuesday, wednesday, thursday, friday, points_date 1 day from alldates where points_date < untildate
)
select
points_date, decode(dayofweek_iso(points_date), 1, monday, 2, tuesday, 3, wednesday, 4, thursday, 5, friday) points
from alldates
order by points_date