How to write a statement that groups data by date but shows when data changed for the first time
date – when data changed
a,b,c – some data and it can be anything
date,a,b,c 04/26/2008,1,1,1 04/25/2008,1,2,1 04/24/2008,1,1,1 04/23/2008,1,1,1 04/22/2008,1,1,1 04/21/2008,2,2,2 04/20/2008,1,1,1
This is should be the result. It might have the same data on different dates, but it missing the next day when data stayed the same.
04/26/2008,1,1,1 04/25/2008,1,2,1 04/22/2008,1,1,1 04/21/2008,2,2,2 04/20/2008,1,1,1
It should work on MS SQL Server 2008 r2
Advertisement
Answer
This is like a gaps and islands problem.
create table yourtablename ([date] date,a int,b int,c int);
insert into yourtablename values
('04/26/2008',1,1,1)
,('04/25/2008',1,2,1)
,('04/24/2008',1,1,1)
,('04/23/2008',1,1,1)
,('04/22/2008',1,1,1)
,('04/21/2008',2,2,2)
,('04/20/2008',1,1,1);
; with cte as
(
select date, a,b,c,combinedstring=cast(a as varchar(max))+ cast(b as varchar(max))+ cast(c as varchar(max))
from yourtablename
)
,cte2 as
(select *,
rn1=row_number() over (partition by combinedstring order by date asc)
,rn2= row_number() over (order by date asc)
from cte
)
select y.*
from
(
select date=min(date)
from cte2
group by (rn2-rn1),combinedstring) t
join yourtablename y
on t.date=y.date