How to group time into a range like 9:00-10:00 AM,10:00-11:AM etc in SSRS charts???

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to group time into a range like...

How to group time into a range like 9:00-10:00 AM,10:00-11:AM etc in SSRS charts???

Rate Topic

Display Mode

Topic Options

Author

Message

var05

Posted Friday, September 07, 2012 3:11 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362

Hi All,

I have a trend chart showing total services per 1 hour time slot across the day. But I wanted to show another chart showing the total services across a time range, say for eg

Time Total Services

8:00 AM 6
9:00 AM 4
10:00 AM 2
11:00 AM 10

Now I have to group the time axis in the below way :

Time Range

8:00 -9:00
9:00 -10:00
10:00-11:00

how to group the time range by 1 hour hour time slot in ssrs

Any help on this??

Thanks
Varsha

Post #1355845

JohnnyDBA

Posted Monday, September 10, 2012 12:21 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:36 AM
Points: 334, Visits: 706

If you're using a DATETIME, you can use the DATEPART function to get the hour and group on the value for the hour based on a 24 hour clock.

DATEPART(hh, GETDATE())

You could then group on the hour number and use some kind of CASE statement to give it a pretty name

CASE
  ...
  WHEN DATEPART(hh, GETDATE()) = 14 THEN '2:00pm-3:00pm'
  WHEN DATEPART(hh, GETDATE()) = 15 THEN '3:00pm-4:00pm'
  ...
END

Hope that helps.

John

Post #1356946

« Prev Topic | Next Topic »

Permissions