Sandwich off days in absents using sql query

Question

I want to mark the off days as absent if the leaves are sandwiched

eg: consider off days (example saturday and sunday) as absent days when day before and after off days is absent.

example:

23-Nov | "Absent"
24-Nov | "Off Day"
25-Nov | "Off Day"
26-Nov | "Absent"

Output required.

23-Nov | "Absent"
24-Nov | "Absent"
25-Nov | "Absent"
26-Nov | "Absent"

I can't fix it to Saturday or sunday because the off days are not the same for all it could be on Mondays & Tuesdays or through out the week. I have the "shiftId" that is '1' for the "off day"

downvote. Not clearly specified the question. no example code for the question. — Alexis Villar, Nov 26 '18 at 08:12
I assume there's some other status that is neither "Off Day" nor "Absent". What is that? Also, we're leaving "Off Day"s alone if *either* the day preceding or the day following is not "Absent"? — Damien_The_Unbeliever, Nov 26 '18 at 08:12
Use [this](https://learn.microsoft.com/en-gb/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) but I don't know if that's what you're looking for. Pretty unclear to me, voted to close — Cid, Nov 26 '18 at 08:15
Search for "gaps and islands" and you will find ways to solve your task. — rpd, Nov 26 '18 at 10:10

score 0 · Answer 1 · answered Nov 26 '18 at 08:25

You have problems finding an algorithm? It's not so hard. Find out whether a record represents a Saturday or Sunday and for these find the neighboring Friday and Monday accordingly. Here is the pseudo code. Use appropriate date functions and LAG and LEAD to convert this into proper SQL.

select
  day,
  case 
    when weekday(day) = Saturday 
     and status(day-1) = 'Absent'
     and status(day+2) = 'Absent' then 'Absent'
    when weekday(day) = Sunday 
     and status(day-2) = 'Absent'
     and status(day+1) = 'Absent' then 'Absent'
    else status
  end as new_status
from mytable;

Sandwich off days in absents using sql query

1 Answers1