CODEWITHSUNDEEP
sql-servergroup-by
pato.llaguno
pato.llaguno

Reputation: 741

Group by DATETIME ranges on another table

I have a table that has 2 columns startdatetime and stopdatetime.

STARTDATETIME             |         STOPDATETIME          |      ID
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            1
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            1
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            2
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            2
2019-05-05T08:00:00Z              2019-05-05T10:00:00Z            2

A second table has a column with eventdatetime.

EVENTDATETIME           |        ID         |       Event
 2019-05-05T05:30:00Z             1                   1
 2019-05-05T05:45:00Z             1                   1
 2019-05-05T07:30:00Z             1                   1
 2019-05-05T07:30:00Z             2                   1
 2019-05-05T07:40:00Z             2                   1
 2019-05-05T07:50:00Z             2                   1

I am trying to do a select in the first table and join the second table where the eventdatetime falls in between the row and get the sum of events. What i don't know how to accomplish is to make a dynamic group by where it will take into consideration the other parameter.

Expected OUTPUT:

 STARTDATETIME             |         STOPDATETIME          |      ID    | SUM(EVENT)
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            1        2
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            1        1
2019-05-05T05:00:00Z              2019-05-05T06:00:00Z            2        0
2019-05-05T07:00:00Z              2019-05-05T08:00:00Z            2        3
2019-05-05T08:00:00Z              2019-05-05T10:00:00Z            2        0

Upvotes: 1

Views: 69

Answers (2)

donPablo
donPablo

Reputation: 1959

SELECT 
      r.ID
      ,r.STARTDATETIME
      ,r.STOPDATETIME
      ,Sum(e.EVENT) as [SUM(Event)]
  FROM dbo.GroupDatetimeRanges as r
  Left Join dbo.GroupDatetimeEvents as e
  on r.ID = e.ID and e.EVENTDATETIME between r.STARTDATETIME and r.STOPDATETIME
  Group By 
        r.ID
      ,r.STARTDATETIME
      ,r.STOPDATETIME

Results

ID  STARTDATETIME           STOPDATETIME           SUM(Event)
1   2019-05-05T05:00:00Z    2019-05-05T06:00:00Z    2
1   2019-05-05T07:00:00Z    2019-05-05T08:00:00Z    1
2   2019-05-05T05:00:00Z    2019-05-05T06:00:00Z    NULL
2   2019-05-05T07:00:00Z    2019-05-05T08:00:00Z    3
2   2019-05-05T08:00:00Z    2019-05-05T10:00:00Z    NULL

Upvotes: 1

Xabi
Xabi

Reputation: 483

Try this code:

select
      f.StartDateTime
    , f.StopDateTime
    , f.ID
    , Qty = sum(
      case 
        when s.EventDatetime between f.StartDateTime and f.StopDateTime
        then s.Event 
        else 0 
      end)
from FirstTable f
left join SecondTable s on (s.ID = f.ID)
group by f.StartDateTime, f.StopDateTime, f.ID

Upvotes: 0

Related Questions