I've been able to use LAG to get data from a previous row but I'm struggling to use it to resolve a more complicated problem.
Essentially for each episode in my table there might be, say, 10 unique attendances. Each attendance has a value of 1, 2 or 3.
What I need to accomplish is when an attendance value increases all of the following rows must have the same value.
e.g.
Episode Attendance Value New Value
12345 1 1 1
12345 2 1 1
12345 3 2 2
12345 4 1 2
12345 5 1 2
12345 6 2 2
12345 7 3 3
12345 8 1 3
12345 9 1 3
12345 10 1 3
34567 1 1 1
34567 2 2 2
34567 3 1 2
34567 4 2 2
34567 5 1 2
34567 6 3 3
34567 7 1 3
56789 1 2 2
56789 2 1 2
56789 3 1 2
So the new value can increase from a 1 to a 2 or 3 and from a 2 to a 3, but it shouldn't ever decrease.
Any help with this would be greatly appreciated. Using LAG might not be the right answer but I'm a bit of a novice as you might have gathered.
Here's the SQL I've got so far:
SELECT *,
NewValue =
CASE WHEN EPISODEID = Lag(EpisodeID,1) OVER(ORDER BY EpisodeID Asc) AND
LAG(Value, 1) OVER(ORDER BY [EpisodeID] asc, Attendance) >= Value THEN LAG(Value,1)
OVER(ORDER BY [EpisodeID] asc, Attendance)
ELSE Value
END
FROM TABLE
It only works for one row and I can see why, but I haven't found enough guidance to improve on it.
Frustratingly, I could do this in Excel but I'm trying to improve my SQL skills
This solution uses a CTE to solve the problem. The CTE checks for what row the counter should increase for each episode group and attendance event. It then joins on to your table and assigns the max value that attendance event should have based on your episode.
;WITH CTE
AS (
SELECT episode
, value
, MIN(attendance) AS row_counter
FROM [your_table]
GROUP BY episode
, value
)
SELECT t.episode
, t.attendance
, t.value
, MAX(cte.value) AS new_Val
FROM [your_table] t
LEFT JOIN CTE
ON cte.episode = t.episode
AND t.attendance >= cte.row_counter
WHERE cte.episode = t.episode
GROUP BY t.episode
, t.attendance
, t.value
ORDER BY t.episode
, t.attendance