All we need is an easy explanation of the problem, so here it is.
I work on sql server 2012 i face issue i can’t make
select statment get Partid from last month until current month
march (3) .
last date exist per
and on same time if there are any
gaps between dates then fill it based on last date
I found partid with last date 2022-01-08 then i will add
08-03-2022 as partid
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
Createddate used below on formate
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 .
what i try
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 ?
;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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
It looks like the problem you’re having is missing data.
When there is no result set for a given date there’s no date to group by.
To work around this you can create an intervals CTE which spans your data range, and then join from it to your data with a LEFT OUTER join. This provides all the intervals in your range to the query
First let’s mock up some data to test with:
DECLARE @mockTable TABLE (InvoiceID INT IDENTITY, invoiceDate DATE, invoiceAmount MONEY) SET NOCOUNT ON WHILE (SELECT COUNT(*) FROM @mockTable) < 300 BEGIN INSERT INTO @mockTable (invoiceDate, invoiceAmount) VALUES (DATEADD(DAY, -ROUND(((89 - 0 -1) * RAND() + 1), 0),CAST(GETDATE() AS DATE)), ((100 - 1 -1) * RAND() + 1) - 1) END
@mockTable now contains 300 rows of randomly generated data, with dates from this year. Using this we can aggregate the total invoices per day, if there are any:
;WITH dateRange AS ( SELECT CAST(CAST(DATEPART(YEAR,CAST(GETDATE() AS DATE)) AS VARCHAR)+'-1-1' AS DATE) AS Interval UNION ALL SELECT DATEADD(DAY,1,Interval) FROM dateRange WHERE Interval <= CAST(GETDATE() AS DATE) ) SELECT dr.interval, SUM(invoiceAmount) AS totalInvoiceAmount, COUNT(invoiceID) AS totalInvoices FROM dateRange dr LEFT OUTER JOIN @mockTable mt ON dr.Interval = mt.invoiceDate GROUP BY dr.Interval OPTION (MAXRECURSION 0)
The CTE called dateRange contains every date from the start of the year, until tomorrow. Joining from it to your data (or the mockTable data) now produces a result for every day in that range, with a NULL totalInvoiceAmount and a 0 totalInvoices count when there were no invoices for that date.
You can apply a similar technique to your data.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂