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

    file gap with dates from last date

    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 


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

    Demo Screen Grab

    You can apply a similar technique to your data. hth




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2