CODEWITHSUNDEEP
sqlapache-spark-sql
dassum
dassum

Reputation: 5113

Sql Between Clause with multiple columns

I am trying to get the dates between JAN 2016 and March 2018. I have used the below query. But it is not returning proper results. The input columns will come at runtime. We cannot convert it into the date as the input columns can contain quarter.

SELECT Year,Month,DayOfMonth 
 FROM period 
 WHERE 
 (Year  >= 2016 and monthofyear  >= 1 ) 
 OR (Year  <= 2018 and monthofyear  <= 3 )  
 GROUP BY  
 Year,Month,DayOfMonth order by year,DayOfMonth;

Period Table

Upvotes: 1

Views: 186

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522007

The logic in your WHERE clause looks to be off. I think you want this:

SELECT Year, Month, DayOfMonth
FROM period
WHERE
    (Year = 2017 AND monthofyear  >= 1) OR
    (Year = 2018 AND monthofyear  <= 3)
GROUP BY
    Year,
    Month,
    DayOfMonth
ORDER BY
    Year,
    DayOfMonth;

That is, the only two allowed years are 2017 and 2018, and each year is handled by separate logic. For 2017, we accept any month from January onwards, and for 2018, we only accept March or earlier.

Edit:

If you instead wanted to cover the range from January 2016 to March 2019, then we can try:

WHERE
    Year IN (2016, 2017) OR
    (Year = 2018 AND monthofyear  <= 3)

Upvotes: 3

Saad Ahmad
Saad Ahmad

Reputation: 403

Use the appropriate cast function to cast your year+month into a true date of year+month+01 and turn into date in format of say YYYYMMDD. You add 01 in your expression. Now the date arithmetic will take care of it

where to_date(year+month+'01','YYYYMMDD') between to_date('20170101','YYYYMMDD') and to_date('20180301','YYYYMMDD')

Abovt is oracle biased SQL but all SQLs will let you turn a year+month into a valid date for first of the month as a true date.

Upvotes: 0

Related Questions