Group by Time Range - 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 2000 Forums

Transact-SQL (2000)

Group by Time Range

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-27 : 12:21:44

Let's say I have rows of data like the following


LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 01:10      100
Sheets   7/27/05 01:20      7/27/05 01:30      100
Sheets   7/27/05 01:25      7/27/05 01:35      100
Sheets   7/27/05 01:40      7/27/05 01:50      100
Tops     7/27/05 02:00      7/27/05 02:10      50
Tops     7/27/05 02:20      7/27/05 02:30      50
Tops     7/27/05 02:25      7/27/05 02:35      50
Tops     7/27/05 02:40      7/27/05 02:50      50
Sheets   7/27/05 03:00      7/27/05 03:10      100
Sheets   7/27/05 03:20      7/27/05 03:30      100
Sheets   7/27/05 03:25      7/27/05 03:35      100

How can I get the following result set?


LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 01:50      400
Tops     7/27/05 02:00      7/27/05 02:50      200
Sheets   7/27/05 03:00      7/27/05 03:35      300

If I do this:


SELECT LoadName,MIN(LoadStartDateTime),MAX(LoadStopDateTime),SUM(LoadWeight) From MeTable
 Group By LoadName

I get this...


LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 03:35      700
Tops     7/27/05 02:00      7/27/05 02:50      200

TIA,

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:42:30

Try:

SELECT LoadName,MIN(LoadStartDateTime),MAX(LoadStopDateTime),SUM(LoadWeight) From MeTable
Group By LoadName, year(LoadStartDateTime), month(LoadStartDateTime), day(LoadStartDateTime), datepart(hour,LoadStartDateTime)

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 12:42:33

Try:

http://www.sqlteam.com/item.asp?ItemID=12654

- Jeff

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 12:43:45

That was close.

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 12:46:06

wow, 3 seconds!

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-27 : 14:02:49

RockMoose: That won't work unless the LoadNames are always in different hours, which in my example is the case but not usually. i.e. All of the loads in the above table could have been timed within one hour.

Jeff - I am looking at that example and am evaluating it. I will see if I can generate the code for it.

Thanks!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 14:30:21

Well, looking at the example results you posted, it looked like You wanted some kind of hourly grouping.

How are You going to identify the different Loads ?
Please say, that it is not by some physical ordering in the table!

For arguments sake, let's say that there was no Tops LoadName in the sample data:
How are You going to know that there are 2 different Sheets loads here ???

LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 01:10      100
Sheets   7/27/05 01:20      7/27/05 01:30      100
Sheets   7/27/05 01:25      7/27/05 01:35      100
Sheets   7/27/05 01:40      7/27/05 01:50      100
Tops     7/27/05 02:00      7/27/05 02:10      50
Tops     7/27/05 02:20      7/27/05 02:30      50
Tops     7/27/05 02:25      7/27/05 02:35      50
Tops     7/27/05 02:40      7/27/05 02:50      50
Sheets   7/27/05 03:00      7/27/05 03:10      100
Sheets   7/27/05 03:20      7/27/05 03:30      100
Sheets   7/27/05 03:25      7/27/05 03:35      100

rockmoose

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-27 : 14:50:32

Yeah, I wasn't too clear on that issue. Well, it's not by physical ordering in the table but it is by the LoadName and StartDateTime, which could be any datetime range. In your above example without the Tops there would only be one sheets record returned in the ResultSet for 700 pounds from 1:00 to 3:35. What I need returned is the MIN(LoadStartDateTime),MAX(StopDateTime), SUM(LoadWeight) every time the LoadName changes for any particular time period. Here's another example....


LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 01:10      100
Sheets   7/27/05 01:02      7/27/05 01:04      100
Sheets   7/27/05 01:06      7/27/05 01:08      100
Sheets   7/27/05 01:10      7/27/05 01:12      100
Tops     7/27/05 01:20      7/27/05 01:22      50
Tops     7/27/05 02:20      7/27/05 02:22      50
Tops     7/27/05 03:20      7/27/05 03:35      50
Tops     7/28/05 01:40      7/28/05 04:45      50
Sheets   7/28/05 04:52      7/28/05 05:01      100
Sheets   7/28/05 03:10      7/28/05 05:30      100
Sheets   7/28/05 06:00      7/28/05 06:35      100

The result set I need from the above would be this...


LoadName LoadStartDateTime LoadStopDateTime LoadWeight
Sheets   7/27/05 01:00      7/27/05 01:12      400
Tops     7/27/05 01:20      7/28/05 04:45      200
Sheets   7/28/05 04:52      7/28/05 06:35      300

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 15:21:16

The streaks method will help You then.

Or a variant like this:

create table #d( LoadName varchar(20), LoadStartDateTime datetime, LoadStopDateTime datetime, LoadWeight numeric(11,2) )
insert #d
select 'Sheets','7/27/05 01:00','7/27/05 01:10',100
union all select 'Sheets','7/27/05 01:20','7/27/05 01:30',100
union all select 'Sheets','7/27/05 01:25','7/27/05 01:35',100
union all select 'Sheets','7/27/05 01:40','7/27/05 01:50',100
union all select 'Tops','7/27/05 02:00','7/27/05 02:10',50
union all select 'Tops','7/27/05 02:20','7/27/05 02:30',50
union all select 'Tops','7/27/05 02:25','7/27/05 02:35',50
union all select 'Tops','7/27/05 02:40','7/27/05 02:50',50
union all select 'Sheets','7/27/05 03:00','7/27/05 03:10',100
union all select 'Sheets','7/27/05 03:20','7/27/05 03:30',100
union all select 'Sheets','7/27/05 03:25','7/27/05 03:35',100


select
	x.LoadName
	,min(x.LoadStartDateTime) as LoadStartDateTime
	,max(x.LoadStopDateTime) as LoadStopDateTime
	,sum(x.LoadWeight) as LoadWeight
from
	(
		select
			LoadName
			,LoadStartDateTime
			,LoadStopDateTime
			,LoadWeight
			,(	select min(LoadStartDateTime)
				from #d t2
				where t1.LoadName <> t2.LoadName
				and t1.LoadStartDateTime < t2.LoadStartDateTime) as nextloadtime
		from #d t1
	) as x
group by
	x.LoadName
	,x.nextloadtime
order by
	min(x.LoadStartDateTime)

When You post questions like these, it's always good if we can get sample data,
not just in text format, but with insert statements etc...

rockmoose

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-27 : 15:32:15

Yeah, that data just came from the top of my head. I will give "real" sample data next time.
I've got the idea now.
Thanks!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 15:43:39

The sample data itself is fine, it's just that it is easier for all of us to help you if you present it in the form of INSERT statements so we can quickly help you out without having to type it all in ourselves..

Ideally, to help troubleshoot an issue, we should be able to copy and paste your code that builds the tables and adds data, then we can just work on the SELECT statement. You'll get a lot more help -- and a lot quicker -- when you make it really easy to help you out.

There's so many times I'd like to help people with simple SELECT statemetns but I just don't have the time to create the tables and enter all the sample data myself .... (back in the day, I used to do it all the time but now I am older and crankier ... )

- Jeff

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2005-07-27 : 15:46:22

I know the feeling and really appreciate the tip! Next time you see something from me you can count on SQL Insert statement.

Thanks again.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-07-27 : 15:47:43

Please do.
Tops!

rockmoose

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-27 : 15:53:02

Rockmoose -- nice variation of my "streaks" technique.

That was even more impressive streaking than Will Ferrell in "Old School".