CODEWITHSUNDEEP
sqlsql-servergroup-by
Pylander
Pylander

Reputation: 1591

SQL Server Group By with Irregular Date Range Criteria

I have two SQL Server tables with the following formats:

events (rows are unique by id)

id,event_of_interest_date
1,2018-01-01
2,2019-07-15
3,2014-01-05

transactions (there are multiple rows per id)

id,transaction_dates
1,2017-02-12
1,2018-01-01
1,2019-02-05
2,2014-03-20
2,2015-03-02
2,2016-05-07
3,2014-01-05
3,2015-02-07
3,2016-04-08

What I am trying to accomplish is to count the number of transactions that occur after the index event date by id to result in this:

id,event_count
1,1
2,0
3,2

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Here is one method using left join and group by:

select e.id, count(t.id)
from events e left join
     transactions t
     on t.id = e.id and
        t.transaction_date > e.event_of_interest_date
group by e.id;

Upvotes: 2

Related Questions