How to get monther per part id from last date to current date and include remaining date ?
How to get monther per part id from last date to current date and include remaining date ?
(OP)
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 --> sqlserver
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 --> sqlserver
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;
RE: How to get monther per part id from last date to current date and include remaining date ?
CODE
Borislav Borissov
VFP9 SP2, SQL Server
RE: How to get monther per part id from last date to current date and include remaining date ?
and give me desired result
i try add new column as lead value
but it repeated value on all partid
and this is wrong
CODE --> sql
and this is my desired result as below
PartId CreatedDate LeadValue
1234 2021-07-03 5
1234 2021-08-03 5
1234 2021-09-03 5
1234 2021-10-05 90
1234 2021-11-05 50
1234 2021-12-05 50
1234 2022-01-05 50
1234 2022-02-05 50
1234 2022-03-05 50
5981 2021-11-15 70
5981 2021-12-03 60
5981 2022-01-03 60
5981 2022-02-03 60
5981 2022-03-03 60
6070 2021-12-12 20
6070 2022-01-08 10
6070 2022-02-08 10
6070 2022-03-08 10
RE: How to get monther per part id from last date to current date and include remaining date ?
RE: How to get monther per part id from last date to current date and include remaining date ?
RE: How to get monther per part id from last date to current date and include remaining date ?
as example partid 1234
partid 1234 have gap on created date for 2 months between 2021-07-03 and 2021-10-05
so it will be
2021-08-03 and leading value 5
2021-09-03 and leading value 5
leading value will take first value of gap start
meaning it will get LEADING value of 2022-07-03 as 5 value
also for part 1234 we need to file gape between last date for part 1234
as 2021-11-05 and current month
so i will get remaining months until current month
then
i will add 4 months with same value of starting gap as 50
1234 2021-12-05 50
1234 2022-01-05 50
1234 2022-02-05 50
1234 2022-03-05 50
leading value will be 50 part 1234 will be 50 from 2021-12-05 to 2022-03-05
because data i will start from it is 2021-11-05 with leading value 50 and this is point of start
and I add date until 05-03-2022 because i add dates until current month based on
partid order by created date
RE: How to get monther per part id from last date to current date and include remaining date ?
CODE
Borislav Borissov
VFP9 SP2, SQL Server
RE: How to get monther per part id from last date to current date and include remaining date ?