How to make select statment get data based on partid from last date with gapes ?
-
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
-
code below give me part from my expected result
because it give me gaps null between dates remaining to get dates until current month
so How to do that please ?
what i try
;with cte as (
select partid, createddate,
dateadd(month, -1,
coalesce(lead(createddate) over (partition by partid order by createddate),
max(createddate) over ()
)
) as end_month
from Parts
union all
select partid, dateadd(month, 1, createddate), end_month
from cte
where createddate <end_month
)
select *
from cte
order by partid, createddate -
In a CTE you could select the date difference between the CreatedDate and the LEAD(CreatedDated, 1, default) where the default is the current month (+1 to deal with boundary crossing). With the number of missing months calculated in the cte you could generate new rows using a tally function
with lead_cte(PartId, CreatedDate, lead_num) as (
select *,
datediff(month,
CreatedDate,
lead(CreatedDate, 1, dateadd(month, 1, cast(getdate() as date)))
over (partition by PartId order by CreatedDate))
from #Parts)
select ld.*, calc.CreatedDateAll
from lead_cte ld
cross apply dbo.fnTally(0, ld.lead_num-1) fn
cross apply (values (dateadd(month, fn.n, ld.CreatedDate))) calc(CreatedDateAll)
order by ld.PartId, calc.CreatedDateAll;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
can you please give me function
dbo.fnTally
to check this query
- Steve Collins wrote:
In a CTE you could select the date difference between the CreatedDate and the LEAD(CreatedDated, 1, default) where the default is the current month (+1 to deal with boundary crossing). With the number of missing months calculated in the cte you could generate new rows using a tally function
with lead_cte(PartId, CreatedDate, lead_num) as (
select *,
datediff(month,
CreatedDate,
lead(CreatedDate, 1, dateadd(month, 1, cast(getdate() as date)))
over (partition by PartId order by CreatedDate))
from #Parts)
select ld.*, calc.CreatedDateAll
from lead_cte ld
cross apply dbo.fnTally(0, ld.lead_num-1) fn
cross apply (values (dateadd(month, fn.n, ld.CreatedDate))) calc(CreatedDateAll)
order by ld.PartId, calc.CreatedDateAll;Awesome job, Steve!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sure, the function code is below. It was taken from this article
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
H2(N) AS ( SELECT 1
FROM (VALUES
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
)V(N)) --16^2 or 256 rows
, H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
, H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
SELECT TOP(@MaxN)
N = ROW_NUMBER() OVER (ORDER BY N)
FROM H8
;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- Jeff Moden wrote:
Awesome job, Steve!
Thank you Professor Moden! Congratulations in advance for passing the 1,000,000 point milestone. I appreciate and learn from you all the time and I hope you get a million more. Cheers Jeff!
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Heh... I haven't been watching but, now that you brought it up and in honor of your great code, here's # 1 million. 😀
It'll take the site software about 20 minutes to figure that out.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's the screenshot! Congratulations again Jeff Moden and THANK YOU for everything 🙂
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thanks for the screen shot and the very kind words. I aim to please... I sometimes miss but I'm always aimin'. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
really very good support
thanks you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply