Skip to content
Advertisement

SQL Group by date with multiple same results

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

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement