declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))
insert @t
select 1, '20120115', 'o'
union all
select 1, '20120215', 'p'
union all
select 1, '20120315', 'o'
union all
select 1, '20120415', 'o'
union all
select 1, '20120515', 'p'
union all
select 1, '20120615', 'p'
union all
select 2, '20120115', 'p'
union all
select 2, '20120215', 'm'
union all
select 2, '20120315', 'p'
union all
select 2, '20120415', 'o'
union all
select 2, '20120515', 'o'
union all
select 2, '20120615', 'o'
select clientid, max(state) as last_state, min(date) as start_date
from (
select *, sum(start_of_group) over(partition by clientid order by date desc) as grp_id
from (
select *, case when state = lag(state) over(partition by clientid order by date desc)
then 0 else 1
end as start_of_group
from @t
) v
) v
where grp_id = 1
group by clientid
|