I have a table1 which has a d_date column and table_2 which has start and end_dates columns and a code column , after joining this matches with the first table on at start and end date values :
So I got below result after joining these two tables as:
`
with cte as (select table_a.d_date, table_2.start_date, table_2.end_date,
table_2.code from
table_a left join table_2 on table_a.d_date = table_2.start_date order by d_date
asc)
select * from cte where year(d_date) =2023
| D_DATE | START_DATE | END_DATE | Code |
|---|---|---|---|
| 2023-01-01 | NULL | NULL | NULL |
| 2023-01-02 | NULL | NULL | NULL |
| 2023-01-03 | NULL | NULL | NULL |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | C |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | D |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | E |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | M |
| 2023-01-05 | NULL | NULL | NULL |
| 2023-01-06 | NULL | NULL | NULL |
| 2023-01-07 | NULL | NULL | NULL |
| 2023-01-08 | NULL | NULL | NULL |
| 2023-01-09 | NULL | NULL | NULL |
| 2023-01-10 | NULL | NULL | NULL |
| 2023-01-11 | NULL | NULL | NULL |
| 2023-01-12 | NULL | NULL | NULL |
| 2023-01-13 | NULL | NULL | NULL |
| 2023-01-14 | NULL | NULL | NULL |
| 2023-01-15 | NULL | NULL | NULL |
| 2023-01-16 | NULL | NULL | NULL |
| 2023-01-17 | NULL | NULL | NULL |
| 2023-01-18 | NULL | NULL | NULL |
| 2023-01-19 | NULL | NULL | NULL |
| 2023-01-20 | NULL | NULL | NULL |
| 2023-01-21 | NULL | NULL | NULL |
| 2023-01-22 | NULL | NULL | NULL |
| 2023-01-23 | NULL | NULL | NULL |
| 2023-01-24 | NULL | NULL | NULL |
| 2023-01-25 | NULL | NULL | NULL |
| 2023-01-26 | NULL | NULL | NULL |
| 2023-01-27 | NULL | NULL | NULL |
| 2023-01-28 | NULL | NULL | NULL |
| 2023-01-29 | NULL | NULL | NULL |
| 2023-01-30 | NULL | NULL | NULL |
| 2023-01-31 | NULL | NULL | NULL |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | M |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | E |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | D |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | C |
| 2023-02-02 | NULL | NULL | NULL |
I need to generate sequence over the d_date column of table_a as shown below, here the start of a sequnce should begin when start_date is matching with d_date and it should continue once it has reached end_date . so sequence of numbers ranging between start_date and end_date
pls note 32,33 , 34 sequnce is coming from previous month
| D_DATE | START_DATE | END_DATE | Code | RES_SEQ |
|---|---|---|---|---|
| 2023-01-01 | NULL | NULL | NULL | 32 |
| 2023-01-02 | NULL | NULL | NULL | 33 |
| 2023-01-03 | NULL | NULL | NULL | 34 |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | C | NULL |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | D | 1 |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | E | Null |
| 2023-01-04 | 2023-01-04 | 2023-01-31 | M | Null |
| 2023-01-05 | NULL | NULL | NULL | 2 |
| 2023-01-06 | NULL | NULL | NULL | 3 |
| 2023-01-07 | NULL | NULL | NULL | 4 |
| 2023-01-08 | NULL | NULL | NULL | 5 |
| 2023-01-09 | NULL | NULL | NULL | 6 |
| 2023-01-10 | NULL | NULL | NULL | 7 |
| 2023-01-11 | NULL | NULL | NULL | 8 |
| 2023-01-12 | NULL | NULL | NULL | 9 |
| 2023-01-13 | NULL | NULL | NULL | 10 |
| 2023-01-14 | NULL | NULL | NULL | 11 |
| 2023-01-15 | NULL | NULL | NULL | 12 |
| 2023-01-16 | NULL | NULL | NULL | 13 |
| 2023-01-17 | NULL | NULL | NULL | 14 |
| 2023-01-18 | NULL | NULL | NULL | 15 |
| 2023-01-19 | NULL | NULL | NULL | 16 |
| 2023-01-20 | NULL | NULL | NULL | 17 |
| 2023-01-21 | NULL | NULL | NULL | 18 |
| 2023-01-22 | NULL | NULL | NULL | 19 |
| 2023-01-23 | NULL | NULL | NULL | 20 |
| 2023-01-24 | NULL | NULL | NULL | 21 |
| 2023-01-25 | NULL | NULL | NULL | 22 |
| 2023-01-26 | NULL | NULL | NULL | 23 |
| 2023-01-27 | NULL | NULL | NULL | 24 |
| 2023-01-28 | NULL | NULL | NULL | 25 |
| 2023-01-29 | NULL | NULL | NULL | 26 |
| 2023-01-30 | NULL | NULL | NULL | 27 |
| 2023-01-31 | NULL | NULL | NULL | 28 |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | M | Null |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | E | Null |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | D | 1 |
| 2023-02-01 | 2023-02-01 | 2023-02-28 | C | Null |
| 2023-02-02 | NULL | NULL | NULL | 2 |
