leading value give me wrong result

ahmed salah 3,216 Reputation points
2022-03-19T12:08:15.48+00:00

i working on sql server 2014 i face issue

leading value is repeated wrong with every gaps of dates added

or the date added before current date

my sample

DECLARE @Parts table 
 (

 PartId int,
 CreatedDate date,
 LeadValue int
 )
 insert into @Parts(PartId,CreatedDate,LeadValue)
 values
 (1234,'20210703',5),
 (1234,'20211005',90),
 (1234,'20211105',50),

 (5981,'20211115',70),
 (5981,'20211203',60),

 (6070,'20211212',20),
 (6070,'20220108',10) 


 DECLARE @CurrentMonth date 
 SET @CurrentMonth= '20220331'
 ;WITH CteTest (PartId,CreatedDate,LeadValue)
 AS
 (
  SELECT PartId,CreatedDate,LeadValue
  FROM (SELECT PartId,CreatedDate,LeadValue, ROW_NUMBER() OVER (PARTITION BY PartId ORDER BY CreatedDate ASC,LeadValue asc) AS RC
        FROM @Parts) Test
  WHERE RC = 1
  UNION ALL
  SELECT CteTest.PartId,
         CASE WHEN EXISTS(SELECT * FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
              THEN (SELECT CreatedDate FROM @Parts Prt WHERE Prt.PartId = CteTest.PartId
                                                    AND YEAR(Prt.CreatedDate)  = YEAR( DATEADD(mm,1,CteTest.CreatedDate))
                                                    AND MONTH(Prt.CreatedDate) = MONTH( DATEADD(mm,1,CteTest.CreatedDate)))
         ELSE DATEADD(mm,1,CteTest.CreatedDate) END AS CreatedDate,LeadValue
  FROM CteTest
  WHERE DATEADD(mm,1,CteTest.CreatedDate) < @CurrentMonth
 )

 SELECT * FROM CteTest
 ORDER BY PartId, CreatedDate  

expected result

PartId CreatedDate LeadValue
1234  2021-07-03        5
1234  2021-08-03        5
1234  2021-09-03        5
1234  2021-10-05       90
1234  2021-11-05       50
1234  2021-12-05       50
1234  2022-01-05       50
1234  2022-02-05      50
1234  2022-03-05      50
5981  2021-11-15      70
5981  2021-12-03      60
5981  2022-01-03      60
5981  2022-02-03      60
5981  2022-03-03      60
6070  2021-12-12      20
6070  2022-01-08     10
6070  2022-02-08     10
6070  2022-03-08     10
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,850 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
{count} votes

Accepted answer
  1. Ronen Ariely 15,096 Reputation points
    2022-03-20T03:19:02.697+00:00

    Hi,

    (1) ISSUE! What if the CreatedDate is 2022-01-30 and you have a gap in Feb 2022... Which Date do you want to get as there is no 2022-02-30 ?!? Your recruitment us full with holes

    (2) it is not well clear why you use the month as the parameter for the gaps. In your description if I have the date 2022-01-31 and next one is 2022-04-01 then you fill the date 2022-02-31 and 2022-03-31 and treat as two months gaps. But if you have date 2022-01-31 and next one is 2022-03-31 then you fill only 2022-02-31 and this one treated as one month gap even so the number of days is almost the same as in the first case. seems strange recruitment to me

    If you want to deal with months that you should probably use only months (for example use first of each month).

    There is no option to cover your exact recruitment as I explain above.

    Here is a solution which provide the first day of the month for the missing gaps.

    ;With Dates as (  
     select TOP 1000 D = DATEADD(Month, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CONVERT(DATE,'2000-01-01'))  
     FROM sys.all_objects t1  
     CROSS JOIN sys.all_objects t2  
    ),  
    CTE as (  
     SELECT t.PartId, t.LeadValue, t.CreatedDate,   
     L = LEAD(t.CreatedDate, 1,DATEADD(MONTH,1, GETDATE())) OVER (PARTITION BY t.PartId ORDER BY t.CreatedDate)  
     FROM Parts t  
    )  
    SELECT t.PartId, t.LeadValue, CreatedDate = d.D--, t.CreatedDate, t.L  
    FROM CTE t  
    LEFT JOIN Dates d ON d.D >= EOMONTH(t.CreatedDate) and d.D < EOMONTH(t.L)  
    GO  
    

    The following solution use the date as you asked and if it does not exists then it use the first day of the month

     ;With Dates as (  
      select TOP 1000 D = DATEADD(Month, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), CONVERT(DATE,'2000-01-01'))  
      FROM sys.all_objects t1  
      CROSS JOIN sys.all_objects t2  
     ),  
     CTE as (  
      SELECT t.PartId, t.LeadValue, t.CreatedDate,   
      L = LEAD(t.CreatedDate, 1,DATEADD(MONTH,1, GETDATE())) OVER (PARTITION BY t.PartId ORDER BY t.CreatedDate)  
      FROM Parts t  
     )  
     SELECT t.PartId, t.LeadValue--, t.CreatedDate  
      ,CreatedDate = CASE  
      WHEN DATEPART(DAY,t.CreatedDate) <= DATEPART(DAY,EOMONTH(d.D))   
      THEN DATEADD(DAY, DATEPART(DAY,t.CreatedDate)-1, d.D)  
      ELSE EOMONTH(d.D)  
      END  
     FROM CTE t  
     LEFT JOIN Dates d ON EOMONTH(d.D) >= EOMONTH(t.CreatedDate) and EOMONTH(d.D) < EOMONTH(t.L)  
     GO  
    

    ----------

    14150-image.pngRonen Ariely
    Personal Site | Blog | Facebook | Linkedin

    0 comments No comments

0 additional answers

Sort by: Most helpful