question

Marid avatar image
Marid asked

Grouping datetime to time inteval slots

I'm using SQL Server 2012.

i have a table Event

ID   StartTime              EndTime
1    2018/06/30 09:03:00    2018/06/30 09:37:00
2    2018/06/30 09:43:00    2018/06/30 09:55:00

I have a temp table - which has time split into 15mins interval for last 24 hours from current time

ID   Istart       Iend
1    09:00        09:15
2    09:15        09:30
3    09:30        09:45
4    09:45        10:00

My output should be - compare the event table (if whole range covered then 1)

ID     Istart   Iend       active
1      09:00    09:15      1
2      09:15    09:30      1
3      09:30    09:45      0.25
4      09:45    10:00      0.75

Please help me to generate SQl query for the above output

datetimetimestampsql server 2012
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If there are more events in your event table, does:
  • Two events that span a 15 min period become a 2 rather than a 1? or should it remain 1
  • Two events, say one thats 12 mins in a period and one that's 3 minutes into a period make a 1? or does it depend where those minutes are?

Also, you're going to have use less than < on the Iend column otherwise you'll have overlap.

Regards,

WRBI

0 Likes 0 ·

1 Answer

· Write an Answer
Jon Crawford avatar image
Jon Crawford answered

Use a minutes table:

Description: developed by Jeremiah Peschka (http://facility9.com/2010/02/24/rounding-to-the-nearest-x-minutes),

returns the closest larger interval within the hour

Business Need: grouping into nearest five,ten,fifteen,thirty minutes easily for reporting

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.