CODEWITHSUNDEEP
sqlsql-server
Sam
Sam

Reputation: 444

Query by a specific month when you only have start and end dates

I need to query a MS SQL table of events by a specific month.

In this scenario, I want to return all events which took place during May 2020.

ID | BAND          | TOUR | LOCATION   | START_DTTM          | END_DTTM
1  | Showaddywaddy | 1    | London     | 2020-04-01 00:00.00 | 2020-06-01 00:00.00
2  | Showaddywaddy | 1    | Manchester | 2020-06-03 00:00.00 | 2020-10-04 00:00.00
3  | Showaddywaddy | 1    | Birmingham | 2020-11-02 00:00.00 | 2020-12-21 00:00.00
4  | Showaddywaddy | 2    | Munich     | 2021-02-27 00:00.00 | 2021-03-04 00:00.00
5  | Showaddywaddy | 2    | Frankfurt  | 2021-04-11 00:00.00 | 2021-06-11 00:00.00
6  | Brian Adams   | 1    | Tokyo      | 2020-05-16 00:00.00 | 2020-08-29 00:00.00
7  | Brian Adams   | 1    | Osaka      | 2020-10-07 00:00.00 | 2020-12-25 00:00.00
8  | Shabba Ranks  | 1    | New York   | 2020-01-13 00:00.00 | 2020-03-22 00:00.00
9  | Shabba Ranks  | 1    | Chicago    | 2020-04-20 00:00.00 | 2020-05-09 00:00.00

I would expect to return ID's 1, 6 and 9.

I'm finding this query quick tricky, so thanks in advance for any help.

Upvotes: 0

Views: 95

Answers (2)

Gabriel Durac
Gabriel Durac

Reputation: 2760

SELECT * 
FROM events
WHERE START_DTTM <= '2020-06-01' 
  AND END_DTTM>='2020-05-01'

This article explains why you can use only 2 conditions to find an overlap

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269743

If you want any overlap with the month, then:

select e.*
from events e
where e.start_dttm < '2020-06-01' and
      e.end_dttm >= '2020-05-01'

And event took place -- at least partly -- in May 2020 if it started before June and started after April.

Although you are not asking, if you wanted a complete overlap, the logic would be quite similar:

select e.*
from events e
where e.start_dttm <= '2020-05-01' and
      e.end_dttm >= '2020-06-01'

Upvotes: 0

Related Questions