Return latest values for each month filling empty values-CodePudding

In SQL Server 2017 I have a table that looks like this https://i.stack.imgur.com/Ry106.png and I would like to get the amount of members at the end of each month, filling out the blank months with the data from the previous month.

So having this table

Create table #tempCenters (
    OperationId int identity (1,1) primary key,
    CenterId int,
    members int,
    Change_date date,
    Address varchar(100), --non relevant
    Sales float --non relevant
)

with this data

INSERT INTO #tempCenters VALUES 
(1, 100, '2020-02-20', 'non relevant column', 135135),
(1, 110, '2020-04-15', 'non relevant column', 231635),
(1, 130, '2020-04-25', 'non relevant column', 3565432),
(1, 180, '2020-09-01', 'non relevant column', 231651),
(2, 200, '2020-01-20', 'non relevant column', 321365),
(2, 106, '2020-03-20', 'non relevant column', 34534),
(2, 135, '2020-06-25', 'non relevant column', 3224),
(2, 154, '2020-06-20', 'non relevant column', 2453453)

I am expecting this result

CenterId, Members, EOM_Date 
1, 100, '2020-2-28'
1, 100, '2020-3-30'
1, 130, '2020-4-31'
1, 130, '2020-5-30'
1, 130, '2020-6-31'
1, 130, '2020-7-31'
1, 130, '2020-8-30'
1, 180, '2020-9-31'
2, 200, '2020-1-31'
2, 200, '2020-2-28'
2, 106, '2020-3-31'
2, 106, '2020-4-30'
2, 106, '2020-5-31'
2, 135, '2020-6-30'

And this is what I´ve got so far

SELECT 
    t.centerId, 
    EOMONTH(t.Change_date) as endOfMonthDate, 
    t.members
FROM #tempCenters t
RIGHT JOIN (
  SELECT 
        S.CenterId, 
        Year(S.Change_date) as dateYear, 
        Month(S.Change_date) as dateMonth, 
        Max(s.OperationId) as id
  FROM   #tempCenters S
  GROUP BY CenterId, Year(Change_date), Month(Change_date)
) A
ON A.id = t.OperationId

which returns the values per month, but not fill the blank ones.

CodePudding user response：

I know it looks cumbersome and I'm sure there is a more elegant solution, but still you can use a combination of subqueries with union all and outer apply to get the desired result.

Select t.CenterId, Coalesce(t.members, tt.members), t.Change_date
From (
Select CenterId, Max(members) As members, Change_date
From
(Select t.CenterId, t.members, EOMONTH(t.Change_date) As Change_date
From #tempCenters As t Inner Join 
(Select CenterId, Max(Change_date) As Change_date
From #tempCenters 
Group by CenterId, Year(Change_date), Month(Change_date)
) As tt On (t.CenterId=tt.CenterId And
            t.Change_date=tt.Change_date)
Union All
Select t.CenterId, Null As member, t.Change_date
From (
Select tt.CenterId, EOMONTH(datefromparts(tt.[YEAR], t.[MONTH], '1')) As Change_date,
       Min_Change_date, Max_Change_date
From (Select [value] as [Month] From OPENJSON('[1,2,3,4,5,6,7,8,9,10,11,12]')) As t, 
                                    (Select CenterId, Year(Change_date) As [YEAR], 
                                            Min(Change_date) As Min_Change_date, Max(Change_date) As Max_Change_date
                                     From #tempCenters Group by CenterId, Year(Change_date)) As tt) As t
Where Change_date Between Min_Change_date And Max_Change_date) As t
Group by CenterId, Change_date) As t Outer Apply 
(Select members 
From #tempCenters 
Where CenterId=t.CenterId And
      Change_date = (Select Max(Change_date) 
                     From #tempCenters Where CenterId=t.CenterId And Change_date<t.Change_date Group by CenterId)) As tt
Order by t.CenterId, t.Change_date

CodePudding user response：

First I get start date (min date) and finish date (max date) for each CenterId. Then I generate all end of months from start date to finish date for each CenterId. Finally I join my subuqery (cte) with your table (on cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date) and get last (previous or same date) members value for each date (end of month).

WITH cte AS (SELECT CenterId, EOMONTH(MIN(Change_date)) AS EOM_Date, EOMONTH(MAX(Change_date)) AS finish
             FROM #tempCenters
             GROUP BY CenterId
             
             UNION ALL 
             
             SELECT CenterId, EOMONTH(DATEADD(MONTH, 1, EOM_Date)), finish
             FROM cte
             WHERE EOM_Date < finish)
             
             
SELECT DISTINCT cte.CenterId, 
       FIRST_VALUE(Members) OVER(PARTITION BY cte.CenterId, cte.EOM_Date ORDER BY tc.Change_date DESC) AS Members, 
       cte.EOM_Date 
FROM cte 
LEFT JOIN #tempCenters tc ON cte.CenterId = tc.CenterId AND cte.EOM_Date >= tc.Change_date
ORDER BY CenterId, EOM_Date;