Problem when using HAVING with aliases in MySQL

Question

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 !!!

Answer 1

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;