SQL Server: loop once a month value to the whole month

Question

I have a table that gets one value of only one day in each month. I want to duplicate that value to the whole month until a new value shows up. the result will be a table with data for each day of the month based on the last known value.

Can someone help me writing this query?

example

score 2 · Answer 1 · answered Nov 26 '20 at 10:04

This is untested, due to a lack of consumable sample data, but this looks like a gaps and island problem. Here you can count the number of non-NULL values for Yield to assign the group "number" and then get the windowed MAX in the outer SELECT:

WITH CTE AS(
    SELECT Yield,
           [Date],
           COUNT(yield) OVER (ORDER BY [Date]) AS Grp
    FROM dbo.YourTable)
SELECT MAX(yield) OVER (PARTITION BY grp) AS yield
       [Date],
       DATENAME(WEEKDAY,[Date]) AS [Day]
FROM CTE;

score 1 · Answer 2 · answered Nov 26 '20 at 13:28

You seem to have data on the first of the month. That suggests an alternative approach:

select t.*, t2.yield as imputed_yield
from t cross apply
     (select t2.*
      from t t2
      where t2.date = datefromparts(year(t.date), month(t.date), 1)
     ) t2;

This should be able to take advantage of an index on (date, yield). And it does assume that the value you want is on the first date of the month.

SQL Server: loop once a month value to the whole month

2 Answers2