I work on sql server 2014 i face issue i can't make select statment get Partid from last month until current month march (3) .
based on last date exist per partid
and on same time if there are any gaps between dates then fill it based on last date
so if
I found partid with last date 2022-01-08 then i will add 08-02-2022 and 08-03-2022 as partid 6070
and if partid with date on month 7 and next month 10 and no date per part id on month 8
and 9 then it must display this gaps according to last month as partid 1234 have gap .
both cases must applied for all data based on partid .
Createddate used below on formate yyyy-mm-dd .
create table Parts
(
PartId int,
CreatedDate date
)
insert into Parts(PartId,CreatedDate)
values
(1234,'2021-07-03'),
(1234,'2021-10-05'),
(1234,'2021-11-05'),
(5981,'2021-11-15'),
(5981,'2021-12-03'),
(6070,'2021-12-12'),
(6070,'2022-01-08')
i need to make select statment display parts as expected result
green rows only for more clear that these parts must added with old parts exist before .
Expected result
| PartId | CreatedDate |
|---|---|
| 1234 | 2021-07-03 |
| 1234 | 2021-08-03 |
| 1234 | 2021-09-03 |
| 1234 | 2021-10-05 |
| 1234 | 2021-11-05 |
| 1234 | 2021-12-05 |
| 1234 | 2022-01-05 |
| 1234 | 2022-02-05 |
| 1234 | 2022-03-05 |
| 5981 | 2021-11-15 |
| 5981 | 2021-12-03 |
| 5981 | 2022-01-03 |
| 5981 | 2022-02-03 |
| 5981 | 2022-03-03 |
| 6070 | 2021-12-12 |
| 6070 | 2022-01-08 |
| 6070 | 2022-02-08 |
| 6070 | 2022-03-08 |
what i try
with cte as (
select partid, month(CreatedDate),
dateadd(month, -1,
coalesce(lead(month(CreatedDate)) over (partition by partid order by month(CreatedDate)),
max(month(CreatedDate)) over ()
)
) as end_month
from Parts
union all
select partid, dateadd(month, 1, month(CreatedDate)) as monthes, end_month
from cte
where monthes < end_month
)
select *
from cte
order by partid, month;