Grouping datetime to time inteval slots

Question

question

Marid asked Jul 2, '18

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

datetime timestamp sql server 2012

1

10 |1200

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

WRBI commented · Jul 02, 2018 at 10:16 AM

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 ·

Answer 1 · 2018-07-02T13:30:07Z

Jon Crawford answered Jul 2, '18

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

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

question