Home > Software engineering >  How to write where clause to consider the earlier date given 2 dates?
How to write where clause to consider the earlier date given 2 dates?

Time:06-12

Control table:

ControlID, Date1, Date2, Date3

Sale table:

ID, ControlID, SaleDate

I want to get the sales from Date1 to which ever date is earlier amongst Date2 and Date3.

SELECT *
FROM SALE S
JOIN CONTROL C ON S.CONTROLID=C.ID
WHERE S.SALEDATE>=C.DATE1 AND S.SALEDATE<EARLIER(DATE2, DATE3)

What is the correct way to write the EARLIER(DATE2, DATE3) logic? For example - implement this as a new scalar function?

Or maybe:

AND S.SALEDATE<C.DATE2 AND S.SALEDATE<C.DATE3

CodePudding user response:

https://xkcd.com/1445

However, even in these cases, understanding the points and creating a good programming habit of SARGable queries is just good business.

  • Related