sql - Finding most recent date based on consecutive dates -

sql - Finding most recent date based on consecutive dates -

i have s table lists absences(holidays) of employees, , find out away today, , date return.

unfortunately, absences aren't given ids, can't retrieve max date absence id if 1 of dates today.

however, absences are given incrementing id per day inputt, need query find employeeid if there entry today's date, increment absenceid column find max date on absence.

table illustration (assuming today's date 11/11/2014, uk format):

absenceid employeeid absencedate 100 10 11/11/2014 101 10 12/11/2014 102 10 13/11/2014 103 10 14/11/2014 104 10 15/11/2014 107 21 11/11/2014 108 21 12/11/2014 120 05 11/11/2014 130 15 20/11/2014 140 10 01/03/2015 141 10 02/03/2015 142 10 03/03/2015 143 10 04/03/2015

so, above, we'd want homecoming dates be:

employeeid returndate 10 15/11/2014 21 12/11/2014 05 11/11/2014

edit: note 140-143 range couldn't included in results appears in future, , none of date range of absence today.

presumably need iterative sub-function running on each entry today's date employeeid matches.

so based on believe you're asking, want homecoming list of people off today , when expected based on holidays have recorded in system, should work on consecutive days.

sql fiddle demo

schema setup:

create table employeeabsence ([absenceid] int, [employeeid] int, [absencedate] datetime) ; insert employeeabsence ([absenceid], [employeeid], [absencedate]) values (100, 10, '2014-11-11'), (101, 10, '2014-11-12'), (102, 10, '2014-11-13'), (103, 10, '2014-11-14'), (104, 10, '2014-11-15'), (107, 21, '2014-11-11'), (108, 21, '2014-11-12'), (120, 05, '2014-11-11'), (130, 15, '2014-11-20') ;

recursive cte generate output:

;with cte ( select employeeid, absencedate dbo.employeeabsence absencedate = cast(getdate() date) union select e.employeeid, e.absencedate cte inner bring together dbo.employeeabsence e on e.employeeid = cte.employeeid , e.absencedate = dateadd(d,1,cte.absencedate) ) select cte.employeeid, max(cte.absencedate) cte grouping cte.employeeid

results:

| employeeid | homecoming date | |------------|---------------------------------| | 5 | november, 11 2014 00:00:00+0000 | | 10 | november, 15 2014 00:00:00+0000 | | 21 | november, 12 2014 00:00:00+0000 |

explanation:

the first select in cte gets employees off today filter:

where absencedate = cast(getdate() date)

this result set unioned employeeabsence table bring together matches employeeid absencedate + 1 day find consecutive days recursively using:

-- add together day cte.absencedate first select e.absencedate = dateadd(d,1,cte.absencedate)

the final select groups cte results employee max absencedate has been calculated per employee.

select cte.employeeid, max(cte.absencedate) cte grouping cte.employeeid

excluding weekends:

i've done quick test based on comment , below modification inner join within cte should exclude weekends when adding days if detects adding day result in saturday:

inner bring together dbo.employeeabsence e on e.employeeid = cte.employeeid , e.absencedate = case when datepart(dw,dateadd(d,1,cte.absencedate)) = 7 dateadd(d,3,cte.absencedate) else dateadd(d,1,cte.absencedate) end

so when add together day: datepart(dw,dateadd(d,1,cte.absencedate)) = 7, if results in saturday (7), add together 3 days instead of 1 monday: dateadd(d,3,cte.absencedate).

sql sql-server greatest-n-per-group