I have a MySQL database with a table containing a list of dates. I need to find the first and last days in a gap in that table. They are the days off certain employee had in the last 30 days. I am using:
Select DATE, LEAD(DATE,1) over(ORDER BY DATE DESC) AS PREVDATE, datediff(DATE,LEAD(DATE,1) over(ORDER BY DATE DESC)) AS GAP from DAYSWORKED DATE>date_sub(CURRENT_DATE, interval 30 DAY) having GAP>1;
If I run the code without the HAVING clause, I get a list of dates and previous dates and the points where GAP>1 are the points where I have the days off, but hwen I include the clause HAVING to filter those points it doesn’t work.
What am I doing wrong?
Thanks for your help !!!
You’ll need to subquery to access the
GAP alias. Also, you need a proper
WHERE clause, which begins with the word “where.”
WITH cte AS ( SELECT DATE, LEAD(DATE) OVER (ORDER BY DATE DESC) AS PREVDATE, DATEDIFF(DATE, LEAD(DATE) OVER (ORDER BY DATE DESC)) AS GAP FROM DAYSWORKED WHERE DATE > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) ) SELECT * FROM cte WHERE GAP > 1;