SQLTeam.com | Weblogs | Forums

How to get part id from last date to current date and include remaining date per monthes?

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;

You already have an awesome answer to thus at How to make select statment get data based on partid from last date with gapes ? – SQLServerCentral Forums

2 Likes