I am struggling with the following issue (simplified form). I have a table with 53 rows and for column [No of Days] only some of the rows contain a quantity. In the column [Days Calculated] I want to dynamically calculate the Days for the rows that do not contain a quantity, adding the difference between 2 rows with a quantity evenly among the rows without quantity. If there is no difference than use the same quantity.

Rows without a quantity before the first row with a quantity should be filled with quantity of this row. Rows without a quantity after the last row with a quantity should be filled with the quantity of this row.

The source I have are the first 2 columns, I need to add the 3rd column.

I have tried to solve this with making a ranking and a case statement with the possible conditions but have not been able to completely get it right. I have the feeling there are better ways.

Any guidance in the right direction is much appreciated !

CodePudding user response：

CREATE TABLE testTable (PeriodNo INT, NoOfDaysPL MONEY);
INSERT INTO dbo.testTable(PeriodNo)
SELECT TOP(53)ROW_NUMBER() OVER (ORDER BY t1.object_id) AS N
FROM sys.all_columns t1
     CROSS JOIN sys.all_columns AS t2;
UPDATE dbo.testTable SET NoOfDaysPL=77 WHERE PeriodNo=22;
UPDATE dbo.testTable SET NoOfDaysPL=70 WHERE PeriodNo=28;
UPDATE dbo.testTable SET NoOfDaysPL=70 WHERE PeriodNo=45;
UPDATE dbo.testTable SET NoOfDaysPL=77 WHERE PeriodNo=51;

SELECT PeriodNo, NoOfDaysPL, 
    CASE WHEN pv IS NULL THEN COALESCE(NoOfDaysPL, ndv)
         WHEN nv IS NULL THEN COALESCE(NoOfDaysPL, pdv)
         WHEN NoOfDaysPL IS NULL THEN pdv-((pdv-ndv)/(nv-pv)*(PeriodNo-pv))
         ELSE NoOfDaysPL 
    END NoOfDaysCalculated
FROM dbo.testTable AS tt
     CROSS APPLY(SELECT MAX(PeriodNo)
                 FROM dbo.testTable AS tt2
                 WHERE tt2.PeriodNo<tt.PeriodNo AND tt2.NoOfDaysPL IS NOT NULL) p(pv)
     CROSS APPLY(SELECT MIN(PeriodNo)
                 FROM dbo.testTable AS tt2
                 WHERE tt2.PeriodNo>tt.PeriodNo AND tt2.NoOfDaysPL IS NOT NULL) n(nv)
     OUTER APPLY(SELECT NoOfDaysPL FROM dbo.testTable AS tt2 WHERE tt2.PeriodNo=p.pv) pc(pdv)
     OUTER APPLY(SELECT NoOfDaysPL FROM dbo.testTable AS tt2 WHERE tt2.PeriodNo=n.nv) nc(ndv);

fiddle