Retrieving Non Null Values Query, returns the previous previous value instead

Question

I've written the following query, which for OccupancyID 182 returns the correct values. However for OccupancyID 3313 with a date of 2024-03-01, it returns 150015 rather than 100812.

The query is trying to meet the need of returning the most recent CustomerVoice_RepairsSatisfactionFeedbackID across the date periods. For OccupancyID 3313, the FeedbackID 150015 was valid for 2023-11-01 and 2023-12-01, but then 100812 came in and is the ID that should be used carrying forward from theen onward until it changes again.

Can anyone see where I'm going wrong?

Expected Results

OccupancyID	FirstDayOfMonth	CustomerVoice_RepairsSatisfactionFeedbackID	PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
182	2023-10-01	NULL	NULL
182	2023-11-01	100571	NULL
182	2023-12-01	NULL	100571
182	2024-01-01	NULL	100571
182	2024-02-01	NULL	100571
182	2024-03-01	NULL	100571
3313	2023-10-01	NULL	NULL
3313	2023-11-01	150015	NULL
3313	2023-12-01	NULL	150015
3313	2024-01-01	100812	150015
3313	2024-02-01	NULL	100812
3313	2024-03-01	NULL	100812 (this shows as 150015 currently which is wrong)

drop table if exists #temp2

CREATE TABLE #temp2
(
    [FirstDayOfMonth] [date] NOT NULL,
    [OccupancyID] [int] NULL,
    [CustomerVoice_RepairsSatisfactionFeedbackID] [int] NULL,
    [RowNumber] [bigint] NULL
)
GO
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 3313, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 3313, 150015, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 3313, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 3313, 100812, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 3313, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 3313, NULL, 6)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-10-01' AS Date), 182, NULL, 1)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-11-01' AS Date), 182, 100571, 2)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2023-12-01' AS Date), 182, NULL, 3)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-01-01' AS Date), 182, NULL, 4)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-02-01' AS Date), 182, NULL, 5)
INSERT #temp2 ([FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID], [RowNumber]) VALUES (CAST(N'2024-03-01' AS Date), 182, NULL, 6)
GO

;WITH CTE AS 
(
    SELECT
        FirstDayOfMonth, 
        OccupancyID, 
        CustomerVoice_RepairsSatisfactionFeedbackID,
        RowNumber,
        LAG(CustomerVoice_RepairsSatisfactionFeedbackID) OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS Lag_Value,
        ROW_NUMBER() OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS RN,
        CASE WHEN CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL THEN 1 ELSE 0 END AS Flag            
    FROM 
        #temp2
)
SELECT 
    OccupancyID, 
    FirstDayOfMonth, 
    CustomerVoice_RepairsSatisfactionFeedbackID,
    CASE 
        WHEN Lag_Value IS NULL THEN PreviousCustomerVoice_RepairsSatisfactionFeedbackID
        ELSE Lag_Value 
    END AS PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
FROM 
(
    SELECT *,
    (
        SELECT 
            TOP(1) CustomerVoice_RepairsSatisfactionFeedbackID 
        FROM 
            CTE c
        WHERE 
            c.Flag = 1 AND 
            c.RN < a.RN AND 
            c.OccupancyID = a.OccupancyID
    ) AS PreviousCustomerVoice_RepairsSatisfactionFeedbackID
    FROM CTE 
a) a

Answer 1

Solution to your problem:

SELECT DISTINCT
    t.OccupancyID, 
    t.FirstDayOfMonth, 
    t.CustomerVoice_RepairsSatisfactionFeedbackID,
    FIRST_VALUE(c.CustomerVoice_RepairsSatisfactionFeedbackID) 
    OVER(PARTITION BY 
                      t.OccupancyID, 
                      t.FirstDayOfMonth, 
                      t.CustomerVoice_RepairsSatisfactionFeedbackID 
         ORDER BY c.ROWNUMBER DESC) 
    AS PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
FROM #temp2 as t
LEFT JOIN #temp2 c
ON t.OccupancyID = c.OccupancyID
AND t.FirstDayOfMonth > c.FirstDayOfMonth
AND c.CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL
ORDER BY t.OccupancyID, 
    t.FirstDayOfMonth

Link to DbFiddle: Dbfiddle

Solution after changing a little bit of your solution:

;WITH CTE AS 
(
    SELECT
        FirstDayOfMonth, 
        OccupancyID, 
        CustomerVoice_RepairsSatisfactionFeedbackID,
        RowNumber,
        LAG(CustomerVoice_RepairsSatisfactionFeedbackID) OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS Lag_Value,
        ROW_NUMBER() OVER(PARTITION BY OccupancyID ORDER BY FirstDayOfMonth) AS RN,
        CASE WHEN CustomerVoice_RepairsSatisfactionFeedbackID IS NOT NULL THEN 1 ELSE 0 END AS Flag            
    FROM 
        #temp2
)
SELECT 
    OccupancyID, 
    FirstDayOfMonth, 
    CustomerVoice_RepairsSatisfactionFeedbackID,
    CASE 
        WHEN Lag_Value IS NULL THEN PreviousCustomerVoice_RepairsSatisfactionFeedbackID
        ELSE Lag_Value 
    END AS PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID
FROM 
(
    SELECT *,
    (
        SELECT 
            TOP(1) CustomerVoice_RepairsSatisfactionFeedbackID 
        FROM 
            CTE c
        WHERE 
            c.Flag = 1 AND 
            c.RN < a.RN AND 
            c.OccupancyID = a.OccupancyID
        ORDER BY RN DESC
    ) AS PreviousCustomerVoice_RepairsSatisfactionFeedbackID
    FROM CTE 
a) a

I just added ORDER BY RN DESC in correlated subquery to take that value of "CustomerVoice_RepairsSatisfactionFeedbackID" which is having highest RN value(in case there are more than 1 value of "CustomerVoice_RepairsSatisfactionFeedbackID" in correlated subquery).

For example, for "OccupancyID" - 3313 and "FirstDayOfMonth" - 2024-03-01, the correlated subquery is giving more than 1 value(i.e.150015 and 100812) and you are taking the TOP(1) value which is giving value as 150015.

For Same value of "OccupancyID" - 3313 and "FirstDayOfMonth" - 2024-03-01, the query is checking whether lag is null or not and in this case it is not null and hence it has taken 100812 as the value and not from the correlated subquery.

Answer 2

Using a kind of gaps and islands request

select [FirstDayOfMonth], [OccupancyID], [CustomerVoice_RepairsSatisfactionFeedbackID],
     max(prev) over(partition by [OccupancyID], grp) [PreviousNonNULLCustomerVoice_RepairsSatisfactionFeedbackID]
  from (
   select *, sum(d) over(partition by [OccupancyID] order by [FirstDayOfMonth] rows unbounded preceding) grp
   from (
      select *,
         case when lag([CustomerVoice_RepairsSatisfactionFeedbackID]) over(partition by [OccupancyID] order by [FirstDayOfMonth]) is null then 0 else 1 end d,
         lag([CustomerVoice_RepairsSatisfactionFeedbackID]) over(partition by [OccupancyID] order by [FirstDayOfMonth]) prev
      from #temp2
   ) t
) t
order by [OccupancyID], [FirstDayOfMonth]

db-fiddle