Group By Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group By Question

Author

Topic

singularity
Posting Yak Master

153 Posts

Posted - 2007-11-27 : 11:35:08

Hello,

How do I write a query that only returns the lines I have in boldface type and rolls up the rest of the rows? The goal is to return the earliest date for each change in status. When I do a min on the date, I lose the 6th row.

id date status
14987 2007-11-14 13:12:12.507 1
14987 2007-11-14 13:12:29.990 2
14987 2007-11-14 13:13:08.787 2
14987 2007-11-14 13:13:26.803 2
14987 2007-11-14 13:13:30.757 4
14987 2007-11-21 10:29:44.913 2
14987 2007-11-21 10:30:52.867 2

Thanks.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2007-11-27 : 12:16:55

You might want to test this out a little bit, I'm not too sure on it.

use tempdb

go

create table temp

(
pk int identity(1,1),
id int,
date datetime,
status int

)

insert into temp
select 14987, '2007-11-14 13:12:12.507' ,1 union all
select 14987 ,'2007-11-14 13:12:29.990' ,2 union all
select 14987 ,'2007-11-14 13:13:08.787' ,2 union all
select 14987 ,'2007-11-14 13:13:26.803' ,2 union all
select 14987 ,'2007-11-14 13:13:30.757' ,4 union all
select 14987 ,'2007-11-21 10:29:44.913' ,2 union all
select 14987 ,'2007-11-21 10:30:52.867', 2


select 
	A.*
	,B.STATUS 
from 
	Temp a
left join 
	Temp b
on b.date = (select 
				top (1) date
			from 
				temp c
			where 
				a.id = c.id 
			and 
				a.date > c.date
			order by 
				date desc)
where 
	a.status != isnull(b.status,'')

You could implement a solution by using a cursor, but I wanted to attempt a set based query. This basically checks your table against the previous record on the status field, if it doesn't equate then the record is returned. I'm sure someone could write a more elegant version.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-27 : 13:07:42

Is this request really to get the first change in status for each ID on a daily basis? If so, extending on cr8nk's example, replace his select statement with this

SELECT id, MIN(date), status
FROM #temp
GROUP BY id, CONVERT(CHAR(10), date, 101), status
ORDER BY id, CONVERT(CHAR(10), date, 101), status

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:38:42

quote:
Originally posted by tfountain

Is this request really to get the first change in status for each ID on a daily basis? If so, extending on cr8nk's example, replace his select statement with this

SELECT id, MIN(date), status
FROM #temp
GROUP BY id, CONVERT(CHAR(10), date, 101), status
ORDER BY id, CONVERT(CHAR(10), date, 101), status

Dont convert dates as varchars. Use

SELECT id, MIN(date), status
FROM temp
GROUP BY id, dateadd(day,datediff(day,0,date),0), status
ORDER BY id, dateadd(day,datediff(day,0,date),0), status

Madhivanan

Failing to plan is Planning to fail

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-28 : 11:27:09

quote:
Originally posted by madhivanan

quote:
Originally posted by tfountain

Is this request really to get the first change in status for each ID on a daily basis? If so, extending on cr8nk's example, replace his select statement with this

SELECT id, MIN(date), status
FROM #temp
GROUP BY id, CONVERT(CHAR(10), date, 101), status
ORDER BY id, CONVERT(CHAR(10), date, 101), status

Dont convert dates as varchars. Use

SELECT id, MIN(date), status
FROM temp
GROUP BY id, dateadd(day,datediff(day,0,date),0), status
ORDER BY id, dateadd(day,datediff(day,0,date),0), status

Madhivanan

Failing to plan is Planning to fail

Good catch, unchecked work (like this) always tend to suffer with bad programming practices :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources