SQL Server Cursor Loop Alternative

Question

I have employee hours log data. Now I have to flag all the records where an employee logs the same hours for the same tasks on the consecutive work days (Sat/Sun weekend).

Let me explain my problem with following example

I have employee hour log table say EMP_HOUR_LOG:

ROW EMP_NO  TASK    DATE        HOURS   FLAG
1   1000    T1  2015-01-01  8   0
2   1000    T1  2015-01-02  8   0
3   1000    T1  2015-01-05  8   0
4   1000    T1  2015-01-06  2   0
5   1000    T2  2015-01-01  4   0
6   1000    T2  2015-01-02  3   0
7   1000    T3  2015-01-09  5   0
8   1000    T3  2015-01-12  5   0
9   1000    T3  2015-01-13  3   0
10  1001    T1  2015-01-14  3   0
11  1001    T1  2015-01-15  3   0

In the above sample dataset I have to update FLAG to 1 for row number 1, 2, 3, 10 and 11, as these records are entries where hours entered by a same employee for the same task on consecutive days is same.

I have already implemented this using cursors as I couldn't think of any alternative to looping through the data record by record.

Please let me know if anyone here can suggest any better way to implement the same by avoiding cursor loops or loops in general.

Thanks

Answer 1

Probably also rows 7 and 8 should have flag = 1. This is the query, but I think the problem has to be handled during the insert:

update e set e.FLAG = 1 
from [dbo].[EMP_HOUR_LOG] e
where exists
(
    select * from [dbo].[EMP_HOUR_LOG] e1 
    where e1.[TASK] = e.[TASK] 
    and e1.[EMP_NO] = e.[EMP_NO]
    and e1.[HOURS] = e.[HOURS]
    and e1.[DATE] in 
        (
        --Next work day
        dateadd(dd, case when DATENAME(dw,e.[DATE]) = 'Friday' then 3 else 1 end, e.[DATE]), 
        --Previous work day
        dateadd(dd, case when DATENAME(dw,e.[DATE]) = 'Monday' then -3 else -1 end, e.[DATE])
        )
)