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

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


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 Comment