Formatting Output / Grouping by hour

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

SQL Server Development (2000)

Formatting Output / Grouping by hour

Author

Topic

netcop
Starting Member

12 Posts

Posted - 2002-09-02 : 07:51:51

Hi there!

I've a dump question. What would be the best way without using cursors to get the following result?

The query gives the following data:
06:04:00
06:19:00
07:17:00
07:24:00
07:32:00
07:54:00
08:02:00
08:09:00
08:24:00
08:39:00
09:09:00
09:24:00

 
hour    minute
6       04  19
7       17  24  32  54
8       02  09  24  39
9       09  24
...

Thanxs in advance
Mike

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 07:58:30

select datepart(hh,fld), sum(datepart(mi,fld))
from tbl
group by datepart(hh,fld)
order by datepart(hh,fld)

or if you want all hours
select a.hr, sum(coalesce(datepart(mi,fld), 0)
from (hr = select 1 union select 2 union slect 3 ... union select 12) as a left outer join tbl
on a.hr = datepart(hh,fld)
group by a.hr
order by a.hr

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

netcop
Starting Member

12 Posts

Posted - 2002-09-02 : 08:20:27

The query with the sum - aggregate function isn't what i exactly wanted. I want the minute values grouped by the hour in which the result is in it.


select    datepart(hh,fld), 
          datepart(mi,fld)
from      tbl 
group by  datepart(hh,fld),
          datepart(mi,fld)
order by  datepart(hh,fld)

This one gives me the correct result.

 
6           05
6           25
6           45
7           05
7           25
7           45
8           05
8           25
8           45
9           05

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 08:35:33

sorry. Didn't look at the results carefully enough.
In that case you don't need the group by as it is grouping all the fields.
Probably should put both in the order by tho.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

netcop
Starting Member

12 Posts

Posted - 2002-09-02 : 09:12:00

Maybe someone can tell me how to bring the result


hour        minutes
6           05
6           25
6           45
7           05
7           25
8           15
8           25

in this form...

 
hour   minutes
6      05  25  45
7      05  25
8      15  25

r937
Posting Yak Master

112 Posts

Posted - 2002-09-02 : 10:04:57

ah, ye olde crosstab

you'll have to do it in a stored proc

i just saw a neat trick this morning in another thread here, pointing to http://www.sqlteam.com/item.asp?ItemID=2368 which shows how to use COALESCE to suppress the comma for the first item in the denormalized comma-delimited list -- in your case you'll have a space delimited list

rudy
http://rudy.ca/

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-02 : 10:44:39

If you do a search on the site I think Rob wrote an article on generating a cross tab from any query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-02 : 12:16:18

I think the cross-tab isn't what you need because you're consolidating everything into one column only. This thread should help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15651

There are several other links in there with variations on Garth's article that Rudy posted earlier, check them all out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources