How to wite select query get data from last date to current dates with gap based...
source link: https://www.codeproject.com/Questions/5327600/How-to-wite-select-query-get-data-from-last-date-t
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
I work on sql server 2012 i face issue i need to make select statment get Partid from last month until current month
based on last date exist per partid
and on same time if there are any gaps between dates then file it based on last date
so
first case if i found partid with last date 2022-01-08 then i will add 08-02-2022 and 08-03-2022 as partid 6070
second case if partid with date on month 7 and month 10 and no date per part id on month 8 and 9 then it must display this gap
according to last month as partid 1234 have gap
both cases must applied for all data based on partid
date 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
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 have tried:
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;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK