CODEWITHSUNDEEP
sqloracle-database
feik
feik

Reputation: 387

SQL - Select where current months falls within a range

I am trying expand my (Oracle) SQL skills and I recently came across this situation and I figured it would be a good example to learn from. I could handle this easily with Python but I would like give it a try in SQL.

I have a application that needs to query a different product depending on the current month. I am having trouble writing the correct sql to get the winter product since the month range crosses the new year, and the basic WHERE statements with greater than and less then will not be enough.

Product  Start_Month  End_Month  
------   -----------  ---------  
Shorts        5           9  
Jackets      10           4

I have been tinkering with some CASE WHEN statements but I haven't got anything worth showing. The below SQL works fine on the summer season, but fails for the winter. It obviously needs some conditional statements, and I have not found any good advanced SQL tutorials with examples. Any advice on how to improve the following SQL to return the correct row? 

SELECT Product FROM SEASONAL_PRODUCTS 
WHERE Start_Month <= extract(month from sysdate) 
AND End_Month > extract(month from sysdate);

Upvotes: 1

Views: 93

Answers (2)

Jrmde
Jrmde

Reputation: 11

Assuming that you don't have ranges of months that are over a year, you could try something like the below code:

SELECT Product
FROM SEASONAL_PRODUCTS 
WHERE sysdate BETWEEN to_date(start_month  || '-'  || extract(YEAR FROM sysdate), 'MM-YY')
AND
  CASE
    WHEN END_MONTH - START_MONTH <= 0
    THEN to_date(end_month  || '-'  || (extract(YEAR FROM sysdate) + 1), 'MM-YY')
    ELSE to_date(end_month  || '-'  || (extract(YEAR FROM sysdate)), 'MM-YY')
  END;

It is a bit messy but its an alternate solution to Juan Carlos Oropeza's answer.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

You need use a double condition because range are different

WHERE  
(     Start_Month < End_Month  
  AND Start_Month <= extract(month from sysdate) AND End_Month >= extract(month from sysdate)
)            
OR   
(     Start_Month > End_Month  
  AND (Start_Month <= extract(month from sysdate) OR End_Month >= extract(month from sysdate)  )
)

enter image description here

Upvotes: 2

Related Questions