Update value in same view using CASE expression

Question

I am creating a view where i have daily data of different items and each item has a state. I created a column that shows what the column was yesterday using

LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS yesterday_state

Now I want to count how long the state has been the same value and im doing so with this:

COUNT (CASE WHEN state = yesterday_state THEN state ELSE NULL END) OVER(PARTITION BY item_id ORDER BY currentdate AS state_age

This is working properly but I need to find a way to set the value back to 0 when state != yesterday_state

This all is happening inside of SELECT statement as I'm creating a view. How could I go around doing this so that the state_age sets to 0 when state is not the same value as yesterday_state

SOLUTION:

CREATE VIEW view AS (
 WITH
 cte1 AS (SELECT *, LAG(state) OVER(PARTITION BY item_id ORDER BY currentdate) AS state_yesterday
 FROM table),
 cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 1 ELSE 0 END AS state_remained
 FROM cte1),
 cte3 AS (SELECT *, CASE WHEN state_remained != 0 THEN SUM(state_remained) OVER(PARTITION BY item_id, state ORDER BY currentdate) ELSE 0 END AS state_age_days
 FROM cte2)
 SELECT *
 FROM cte3
)

Answer 1

Schematically

WITH 
cte1 AS ( SELECT *, LAG(state) OVER (ORDER BY datetime) AS prev_state 
 FROM sourcetable),
cte2 AS ( SELECT *, CASE WHEN state = prev_state THEN 0 ELSE 1 END AS state_changed 
 FROM cte1 ),
cte3 AS ( SELECT *, SUM(state_changed) OVER (ORDER BY datetime) AS group_number 
 FROM cte2 )
SELECT *, COUNT(*) OVER (PARTITION BY group_number) AS group_count 
FROM cte3

For multiple entities add proper partitioning / partitioning level.

• Itzblend: Thanks for the answer but i think there is still something im missing. The group number and group count just keep static over the rows. I use the following code: WITH cte1 AS (SELECT *, LAG(state) OVER(ORDER BY currentdate) AS state_yesterday FROM table), cte2 AS (SELECT *, CASE WHEN state = state_yesterday THEN 0 ELSE 1 END AS state_changed FROM cte1), cte3 AS (SELECT *, SUM(state_changed) OVER(ORDER BY currentdate) AS group_number FROM cte2) SELECT *, COUNT(*) OVER(PARTITION BY group_number) AS group_count FROM cte3

Answer 2

You can find the "groups" where state has not changed like:

SELECT t.* 
 , ROW_NUMBER() OVER (PARTITION BY item_id
 ORDER BY currentdate)
 - ROW_NUMBER() OVER (PARTITION BY item_id, state
 ORDER BY currentdate) as grp
FROM table t

You can then apply whatever aggregate you want to on this group. Example:

SELECT MIN(currentdate) OVER (PARTITION BY item_id, grp) as FIRST_DATE
 , MAX(currentdate) OVER (PARTITION BY item_id, grp) as LAST_DATE
 , COUNT(1) OVER (PARTITION BY item_id, grp) as #SIZE
 , t.itemid, t.state
FROM ( 
 SELECT t.* 
 , ROW_NUMBER() OVER (PARTITION BY item_id
 ORDER BY currentdate)
 - ROW_NUMBER() OVER (PARTITION BY item_id, state
 ORDER BY currentdate) as grp
 FROM table t
);