CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
HoneyBadger786
HoneyBadger786

Reputation: 89

Retrieve all Records for a particular Month using start_end and end_date

I have the a table with the follow structure and data

Product           startdate         enddate 
ProdA             01/01/2020        15/01/2020
ProdB             29/01/2020        02/02/2020
ProdC             02/02/2020        03/03/2020
ProdD             20/12/2019        20/12/2020

I need an sql query for redshift that returns me all the product which were valid for a month I provide. The validity for a month is considered if that product had any day for that month in between startdate and enddate

so if I am passing the date 01/01/2020 (This can be modified based on solution if you want to format it to YYYY-mm for example.) I am looking for records which were valid in January 2020 and would expect the following products returned

ProdA ProdB ProdD

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use this logic:

where startdate <= last_day('2020-01-01') and
      enddate >= '2020-01-01'

That is, there is an overlap if the start date is before the end of the month and the end date is on or after the start of the month.

Upvotes: 2

Related Questions