CODEWITHSUNDEEP
sql
mightyowl_v
mightyowl_v

Reputation: 9

Querying for price of a product within a date range

enter image description here

I have to find the average price of iPhone Xs from this table within 1 August 2021 and 31 August 2021. So the records that I have narrowed down are 8, 9, 11 and 12. However records 8 and 9's StartDateTime and EndDateTime are outside of 1st and 31st August 2021, but still run through August 2021.

So far, I am only able to query for records 11 and 12. Which is more straightforward:

SELECT *
FROM dbo.PriceHistory
WHERE 
  PName = 'iPhone Xs' 
  AND StartDateTime BETWEEN '2021-08-01 00:00:00.000' AND '2021-08-31 00:00:00.000';

How should I query so that I can get records 8 and 9 as well?

Upvotes: 0

Views: 156

Answers (1)

Nicholas Weston
Nicholas Weston

Reputation: 629

Based on the desired behaviour you've described, you probably want to check if the start date is before September 1, and the end date is after August 1. This will get all items whose date ranges overlap with August.

So:

SELECT *
FROM dbo.PriceHistory
WHERE 
  PName = 'iPhone Xs' 
  AND StartDateTime < '2021-09-01 00:00:00.000'
  AND EndDateTime >= '2021-08-01 00:00:00.000';

(Note that the upper endpoint should be September 1 to ensure we include the last day of August.)

Upvotes: 3

Related Questions