0

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

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
adrianolsantos
  • 21
  • 2
  • `it doesn't work` ... what is the exact error message you are getting? Your query is missing a `WHERE` clause, but maybe this is just a copying error. – Tim Biegeleisen Jun 25 '21 at 06:13
  • please show your actual query and explain what you mean by "it doesn't work"; what _does_ happen? please also edit your question to show output (as text, not an image) of `show create table DAYSWORKED` and some insert statements for sample data and desired and actual results for that sample data that demonstrate the problem – ysth Jun 25 '21 at 06:13
  • 1
    `GAP` column contains window function which is not allowed in HAVING clause, even implicitly. Because it affects the output rowset and hence it affects window function (self) result which is obtained between HAVING and ORDER BY. – Akina Jun 25 '21 at 06:31

1 Answers1

2

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • you can use aliases from a having clause. they've got something else going wrong – ysth Jun 25 '21 at 06:11
  • @ysth I think you're right. I have asked the OP for clarification. My answer should still be valid at least, but I'll delete upon seeing more information. – Tim Biegeleisen Jun 25 '21 at 06:13
  • I'm wondering now if aliases which include analytic function calls can in fact be used in the `HAVING` clause. – Tim Biegeleisen Jun 25 '21 at 06:15