August 22, 2005 at 9:11 am
Could someone help me with this please, I am trying to group some data in 1 hourly blocks, ignoring the dates:
ID CreatedON
1 21/08/2005 08:00
2 21/08/2005 09:30
3 21/08/2005 09:45
4 22/08/2005 08:30
5 22/08/2005 10:02
etc etc
desired results
Time Occr
00:00-00:59 0
01:00-01:59 0
etc etc
08:00-08:59 2
09:00-09:59 1
10:00-10:59 1
and so on
any help would be greatly appreciated.
August 22, 2005 at 9:26 am
Select DATEPART(hh, CrDate) as Hour, count(*) as Total from dbo.SysObjects where XType = 'IF' GROUP BY DATEPART(hh, CrDate)
August 22, 2005 at 9:56 am
Thank you Remi, that was a great help, just one more question, is there any way of simplyfying the following rather long-winded statement to produce the desired results:
Select left('00',2-len(DATEPART(hh, Date1)))
+ convert(varchar,DATEPART(hh, Date1))
+ ':00 - '
+ left('00',2-len(DATEPART(hh, Date1)))
+ convert(varchar,DATEPART(hh, Date1))
+ ':' + '59' as Hour,
count(*) as Total
from temp_table
GROUP BY left('00',2-len(DATEPART(hh, Date1)))
+ convert(varchar,DATEPART(hh, Date1))
+ ':00 - '
+ left('00',2-len(DATEPART(hh, Date1)))
+ convert(varchar,DATEPART(hh, Date1))
+ ':' + '59'
results
Hour Total
00:00 - 00:59 5
07:00 - 07:59 23
12:00 - 12:59 1
13:00 - 13:59 2
14:00 - 14:59 1
15:00 - 15:59 1
20:00 - 20:59 1
once again, thank you for your help
August 22, 2005 at 9:59 am
Yup : application's job. String manipulation is much easier on vb 6 and .net than sql server. And it is not the job of the server to do presentation manipulation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply