Get employee with on-off-on weekend work pattern

Question

I have an employee table which has columns like employee_ID, punch_in_date, punch_out_date.

Now, what I need is to find those employees who have worked on-off-on weekend pattern.

It is like if an employee has worked in week1 then he/she should not have worked in week2 and must have worked in Week3. Week1, week2, and week3 are the consecutive weekend days.

I tried using the lag function of sql.

    SELECT   employee_id, 
         punch_in_date, 
         Lag(punch_in_date) OVER(partition BY employee_id ORDER BY employee_id)                              AS week_lag,
         Datediff(day,Lag(punch_in_date) OVER(partition BY employee_id ORDER BY employee_id) ,punch_in_date) AS days
FROM     employee 
WHERE    Datediff(day,Lag(punch_in_date) OVER(partition BY employee_id ORDER BY employee_id) ,punch_in_date)>= 14
AND      datediff(day, punch_in_date, 'Today's date') <= 90 /*This means the data must falls under 3 months duration*/;

But I am getting an error like

SQL Error [4108] [S0001]: Windowed functions can only appear in the SELECT or ORDER BY clauses.

How can I get the required result?

sample data:

employee_ID |punch_in_date |punch_out_date |
------------|--------------|---------------|
2           |2015-12-05    |2015-12-05     |
2           |2015-12-12    |2015-12-12     |
2           |2015-12-19    |2015-12-19     |
2           |2016-01-02    |2016-01-02     |
2           |2016-01-23    |2016-01-24     |
2           |2016-01-24    |2016-01-25     |
2           |2016-01-30    |2016-01-30     |
2           |2016-02-06    |2016-02-06     |
2           |2016-02-06    |2016-02-06     |
2           |2016-02-06    |2016-02-07     |
2           |2016-02-13    |2016-02-14     |
2           |2016-02-27    |2016-02-28     |
2           |2016-03-12    |2016-03-13     |

Using `datediff(day, punch_in_date, GETDATE()) <= 90` will prevent SQL Server from using any indexes on the `punch_in_date` column. Using `punch_in_date >= dateadd(day,-90,getdate()` will provide the same functionality without preventing index use. This is known as [sargability](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) — iamdave, Aug 20 '18 at 08:44
It would be nice if you could add some sample data and expected result as well — holder, Aug 20 '18 at 08:53
I have edited the question with the sample data of an employee @holder — Alesh, Aug 20 '18 at 09:06

holder · Answer 1 · 2018-08-20T10:40:03.647

1

As the error message states; Windowed function are only allowed in select and order by. What you can do is to use your query in a subquery

Select Employee_id,punch_in_date, week_lag,[days] FROM(
 SELECT   employee_id, 
         punch_in_date, 
         Lag(punch_in_date) OVER(partition BY employee_id ORDER BY employee_id)                              
AS week_lag,
         Datediff(day,Lag(punch_in_date) OVER(partition BY employee_id ORDER BY 
employee_id) ,punch_in_date) AS [days]
FROM     employee 
where punch_in_date >= dateadd(day,-90,getdate())
) q
WHERE    [days]>= 14

edited Aug 20 '18 at 10:40

answered Aug 20 '18 at 08:37

holder

585
2
9

Using `datediff(day, punch_in_date, GETDATE()) <= 90` will prevent SQL Server from using any indexes on the `punch_in_date` column. Using `punch_in_date >= dateadd(day,-90,getdate()` will provide the same functionality without preventing index use. This is known as [sargability](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) – iamdave Aug 20 '18 at 08:43
I am getting error Incorrect syntax near where while running this query. @holder – Alesh Aug 20 '18 at 08:58
@Alesh MS SQL requires that a sub-query has an alias. So just put an alias name like f.e. `q` after the round bracket. – LukStorms Aug 20 '18 at 09:03
1

@holder Perhaps that criteria for `punch_in_date` could be put in the sub-query. It might improve the performance of the query. – LukStorms Aug 20 '18 at 09:10

score 1 · Accepted Answer · answered Aug 20 '18 at 11:23

I suspect you want:

select employee_id,  punch_in_date, week_lag        
       datediff(day, week_lag, punch_in_date) AS days
from (select e.*,
             lag(punch_in_date) over (partition by employee_id order by employee_id) as week_lag
      from employee e
     ) e
where week_lag >= 14 and
      datediff(day, punch_in_date, getdate()) <= 90 ;

When using window functions, be very careful about where filtering. The filters are applied before the window function, so you might miss some rows that you want.

Get employee with on-off-on weekend work pattern

2 Answers2