How to write select statment that get data based on partid from last date to current date with filling gapes?
I work on sql server 2012 i face issue i can't make
select statmentget Partid from last month until current month
last dateexist per
and on same time if there are any
gaps between datesthen fill it based on last date
I found partid with last date 2022-01-08 then i will add
partidwith date on month
7and next month
10and no date per
part idon month 8
and 9 then it must display this
gapsaccording to last month as partid 1234 have gap .
both cases must applied for all data based on
Createddateused below on formate
create table Parts (
insert into Parts(PartId,CreatedDate)
i need to make
partsas expected result
green rowsonly 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
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 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. hth