CASE and GROUP BY Using DATETIME

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 2008 Forums

Transact-SQL (2008)

CASE and GROUP BY Using DATETIME

Author

Topic

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2011-04-08 : 03:20:10

Table:
[DATETIME] [DUE]
2011-01-01 12:02:03 100
2011-01-01 12:09:03 100
2011-01-01 12:15:03 100
2011-01-01 12:24:03 100
2011-01-01 12:45:03 100
2011-01-01 12:54:03 100
2011-01-01 13:02:03 100
2011-01-01 13:09:03 100
2011-01-01 13:15:03 100
2011-01-01 13:24:03 100
2011-01-01 13:45:03 100
2011-01-01 13:54:03 100
2011-01-01 15:02:03 100
2011-01-01 15:09:03 100
2011-01-01 15:15:03 100
2011-01-01 15:24:03 100
2011-01-01 15:45:03 100
2011-01-01 15:54:03 100
2011-01-01 14:02:03 100
2011-01-01 14:09:03 100
2011-01-01 14:15:03 100
2011-01-01 14:24:03 100
2011-01-01 14:45:03 100
2011-01-01 14:54:03 100

In above table Datetime repeats the Date with Time. I need the above report with Grouping Date by 12-13, 13-14 etc..

Output:
[Hourly] [DUE]
12-13 600
13-14 600
14-15 600

Help me to built this output from suitable Query...

Regards,
Kalaiselvan R
Love Yourself First....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-08 : 04:13:31

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, [DATETIME]), 0), SUM(DUE)
FROM dbo.Table1
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, [DATETIME]), 0)
ORDER BY DATEADD(HOUR, DATEDIFF(HOUR, 0, [DATETIME]), 0)

N 56°04'39.26"
E 12°55'05.63"

cns1202
Starting Member

7 Posts

Posted - 2011-04-08 : 04:23:19

select CAst(MIN(DATEPART(hh,[DATETIME])) as varchar(10)) +'-'+ CAST(MIN(DATEPART(hh,[DATETIME])) + 1 as varchar(10)) as Timeinterval,
SUM([due]) from tblDtime
group by Datepart(hh,[DATETIME])

Regards,
Chirag Shah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources