CODEWITHSUNDEEP
sqlgoogle-bigquery
babycoder
babycoder

Reputation: 180

Filtering of SQL Query using multiple ANDs

I have a Big Query query:

SELECT
*
FROM tableE e
WHERE e.dateStart < CURRENT_TIMESTAMP()
AND e.dateEnd > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND TIMESTAMP_DIFF(endDate, startDate, DAY) > 7
LIMIT 1000;

and I am trying to get a list of events that started within a 7 day period and further filter out those events where the end date is AFTER 7 days of the start date.

However, when I run this query, I sometimes get results for events that happened in previous years (2011...2019).

What am I doing wrong? Is there another way to re-write what I am trying to do?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I would approach this using direct date comparisons:

SELECT e.*
FROM tableE e
WHERE e.dateStart < CURRENT_TIMESTAMP() AND
      e.dateStart > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY) AND
      e.dateEnd > TIMESTAMP_ADD(startDate, INTERVAL 7 DAY)
LIMIT 1000;

However, your issue is that the first two comparisons should both be on dateStart.

Note: This assumes that dateEnd can be in the future. Otherwise, you will get no rows.

Upvotes: 1

Related Questions