I have a table in SSMS:
Id Date Value
111 1/1/18 x
111 1/2/18 x
111 1/3/18 y
111 1/4/18 y
111 1/5/18 x
111 1/6/18 x
222 1/3/18 z
222 1/6/18 y
222 1/8/18 y
I want to count for the frequency of latest value . So the output will be:
Id Value Days
111 x 2 *(for 1/5/18 & 1/6/18)*
222 y 3 *(for 1/6/18 & 1/8/18; Here I assume 1/7/18 is a weekend or holiday. Even though my table skips the weekend, we still want to count days for the weekend)*
How would this be done? Many thanks!
Use lag to get the previous row's value and then a running sum to assign groups. Thereafter count the number in the first group.
select id,val,datediff(day,min(date),max(date))+1 as days
from (select t.*,sum(case when val=prev_val then 0 else 1 end) over(partition by id order by date desc) as grp
from (select t.*,lag(val) over(partition by id order by date desc) as prev_val
from tbl t
) t
) t
where grp=1
group by id,val