ahmedsa2018
Programmer
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 .
th cases must applied for all data based on partid .
Createddate used below on formate yyyy-mm-dd .
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
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 .
th cases must applied for all data based on partid .
Createddate used below on formate yyyy-mm-dd .
Code:
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
Code:
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;