CODEWITHSUNDEEP
sqlsql-server
fledgling
fledgling

Reputation: 1051

SQL query to categorize time series data

I have a table which looks like this:

ID   ScheduledPickUpTime   PickUpTime    ScheduledDropTime   DropTime
--  --------------------   ----------    -----------------   --------

1       00:10                 00:30         03:10             03:30
2       01:10                 02:10         03:05             03:10
3       02:30                 02:45         05:20             07:00
4       08:00                 08:45         13:00             14:00
5       11:20                 12:00         20:00             21:00

I need to display the information in the below format, with times spanning from 12 AM to 12 PM and each time field column counts in the time span.

TimeData               00:00-01:00   01:00-02:00  02:00-03:00  03:00-04:00  ....

ScheduledPickUpTime     1            1            1             0
PickUpTime              1            0            2             0
ScheduledDropTime       0            0            0             2
DropTime                0            0            0             2

I need help with creating the sql query which does this. Thanks in advance.

Update:

The table has an additional EventType column. This column needs to be unpivoted and the counts for each EventType is based on the PickUpTime column 

ID   ScheduledPickUpTime   PickUpTime    ScheduledDropTime   DropTime   EventType
--  --------------------   ----------    -----------------   --------

1       00:10                 00:30         03:10             03:30     Call
2       01:10                 02:10         03:05             03:10     Email
3       02:30                 02:45         05:20             07:00     Email  
4       08:00                 08:45         13:00             14:00     Call    
5       11:20                 12:00         20:00             21:00     Routine

The output in this case should be

TimeData               00:00-01:00   01:00-02:00  02:00-03:00  03:00-04:00  ....

ScheduledPickUpTime     1            1            1             0
PickUpTime              1            0            2             0
ScheduledDropTime       0            0            0             2
DropTime                0            0            0             2
Call                    1            0            0             0
Email                   0            0            2             0
Routine                 0            0            0             0

The values in the EventType column is dynamic and can have more such variations.

Is it possible to achieve this? Thanks!

Upvotes: 1

Views: 423

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

This is rather complicated. It is a pivot and an unpivot:

select TimeData,
       sum(case when t >= '00:00' and t < '01:00' then 1 else 0 end) as [00:00-01:00],
       sum(case when t >= '01:00' and t < '02:00' then 1 else 0 end) as [01:00-02:00],
       sum(case when t >= '02:00' and t < '03:00' then 1 else 0 end) as [02:00-03:00],
       . . .
from t apply
     (values ('ScheduledPickUpTime', ScheduledPickUpTime, 1),
             ('PickUpTime', PickUpTime, 2),
             ('ScheduledDropTime', ScheduledDropTime, 3),
             ('DropTime', DropTime, 4)
     ) v(TimeData, t, ordering)
group by TimeData, ordering
order by ordering;

Upvotes: 1

Related Questions