divide the timestamp into 15 minutes interval

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

General SQL Server Forums

New to SQL Server Programming

divide the timestamp into 15 minutes interval

Author

Topic

Harinder
Starting Member

1 Post

Posted - 2011-05-19 : 06:36:50

hi!
I have a table bookings having a PK as bookingNbr, operatorName and a general date/time field 'timestamp'. I need to run a crosstab query to calculate total bookings by the operator(column header) in 15 minutes interval(row header) for a date range (10-05-2011 to 15-05-2011). Please help me
Thanks.
Harinder

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-19 : 06:51:59

Did you want to truncate or round? This will truncate to the 15 minute interval:


DATEADD(mi,DATEDIFF(mi,0,timestamp_column)/15*15,0)

To generate cross-tab, use PIVOT operator if you are on SQL 2005 or higher and if your intervals are known in advance. If not, you will need dynamic pivot, see Madhivanan's blog here: http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources