leading value give me wrong result

Question

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

Answer 1

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  

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin