Create A shift summary, with a shift spanning over 2 days

I have a data set like the sample below. I need to query and summarize the number of transactions per shift. The problem is that the shift cycle does not align with the normal hours of a day.

Eg. The shift for day 2020-01-01 will start at 06:00 AM on 2020-01-01 and end at 06:00 AM on 2020-01-02

Below are an sample data set.

| EntityID | TransactionDateTime     |
|----------|-------------------------|
| 1        | 2020-01-01 06:53 |
| 1        | 2020-01-01 06:01 |
| 1        | 2020-01-01 13:05 |
| 1        | 2020-01-02 05:15 |
| 1        | 2020-01-02 06:14 |
| 1        | 2020-01-02 08:35 |
| 1        | 2020-01-02 01:42 |
| 2        | 2020-01-01 05:01 |
| 2        | 2020-01-01 06:01 |
| 2        | 2020-01-01 13:12 |
| 2        | 2020-01-02 05:53 |
| 2        | 2020-01-02 08:24 |

The desired results need to look like this.

| EntityID | Shift Date | Num Of Transactions |
|----------|------------|---------------------|
| 1        | 2020-01-01 | 4                   |
| 1        | 2020-01-02 | 3                   |
| 2        | 2019-12-31 | 1                   |
| 2        | 2020-01-01 | 3                   |
| 2        | 2020-01-02 | 1                   |

Any help will greatly be appreciated.

Answer

You can just add a 6 hours offset to the transaction date, and then aggregate like so:

select 
    EntityID, 
    cast(dateadd(hour, -6, TransactionDateTime) as date) ShiftDate, 
    count(*) NumOfTransactions
from mytable
group by EntityID, cast(dateadd(hour, -6, TransactionDateTime) as date)

Advertisement

Answer