ssrs 2012 - SQL - Returning the Top two Results for Each Match

Question

I have a situation where we record every change to a column of a member record in the MEMBER table as a separate row. The changes that are logged only show the column name that was changed, the new value and the date the change was made.

Example: Table Name - CustomerChanges

customerId	columnName	newValue	dateChanged
1234	status	Active	1/12/2021
1234	status	Cancelled	9/30/2020
1234	status	Frozen	7/1/2020
1234	status	Active	1/1/2020
5678	status	Active	1/11/2021
5678	status	Frozen	11/1/2020
5678	status	Active	2/1/2020
9101	status	Active	1/10/2021
9101	type	Full Time	1/10/2021
9101	status	Frozen	10/15/2020
9101	status	Active	1/1/2020

Answer 1

This is a bit messy, there is probably a better way of doing it but this should work.

You will have to adjust the inner-most query to filter on your date range, I didn't include that bit.

I recreated your dataset and then produced the output as follows

DECLARE @t TABLE (customerId int, columnName varchar(20), newValue varchar(20), dateChanged date)
INSERT INTO @t VALUES 
(1234, 'status', 'Active'   , '2021-01-12' ),
(1234, 'status', 'Cancelled', '2020-09-30' ),
(1234, 'status', 'Frozen'   , '2020-07-01' ),
(1234, 'status', 'Active'   , '2020-01-01' ),
(5678, 'status', 'Active'   , '2021-01-11' ),
(5678, 'status', 'Frozen'   , '2020-11-01' ),
(5678, 'status', 'Active'   , '2020-02-01' ),
(9101, 'status', 'Active'   , '2021-01-10' ),
(9101, 'type', 'Full Time'  , '2021-01-10' ),
(9101, 'status', 'Frozen'   , '2020-10-15' ),
(9101, 'status', 'Active'   , '2020-01-01' )

--SELECT * FROM @t

SELECT * FROM 
    (
    SELECT
        s.* 
        , LAG(newValue, 1) OVER(PARTITION BY customerId ORDER BY dateChanged) AS PreviousStatus 
        FROM 
        (SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY dateChanged DESC) as RowN
            FROM @t 
            WHERE columnName = 'Status' -- Add date filtering here
        ) s
        WHERE s.RowN < =2
    ) x 
    WHERE newValue = 'Active' AND PreviousStatus = 'Frozen'

This gives the following output