How to write select statment that get data based on partid from last date to current date with filling gapes?

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) .

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 .

 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

How to write select statment that get data based on partid from last date to current date with filling gapes?

what i try

updated post

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.

Method 1

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.

How to write select statment that get data based on partid from last date to current date with filling gapes?

You can apply a similar technique to your data.
hth

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply