Search code examples
sqlsql-serverdategaps-in-data

How to pivot two date columns and fill in the gaps with multiple overlapping date periods

I have a table with employee absence entries. The rows contain employee number, first and last day of absence and a whole lot of more data like absence type, approved, etc.

absencecalendarline:

EMPLOYEENUMBER | FIRSTDAYOFABSENCE | LASTDAYOFABSENCE | ABSENCETYPE | APPROVED
---------------+-------------------+------------------+-------------+----------
1              | 2013-01-01        | 2013-01-04       | VACATION    | TRUE
2              | 2013-01-01        | 2013-01-02       | VACATION    | TRUE
3              | 2013-02-05        | 2013-02-08       | VACATION    | TRUE
2              | 2013-02-06        | 2013-02-07       | VACATION    | TRUE

I would like to create a view with the absence entries listed with the all the dates. Something like this.

desired result:

EMPLOYEENUMBER | ABSENCEDATE | ABSENCETYPE | APPROVED
---------------+-------------+-------------+----------
1              | 2013-01-01  | VACATION    | TRUE
1              | 2013-01-02  | VACATION    | TRUE
1              | 2013-01-03  | VACATION    | TRUE
1              | 2013-01-04  | VACATION    | TRUE
2              | 2013-01-01  | VACATION    | TRUE
2              | 2013-01-02  | VACATION    | TRUE
3              | 2013-02-05  | VACATION    | TRUE
..               ..            ..            ..
3              | 2013-02-08  | VACATION    | TRUE
2              | 2013-02-06  | VACATION    | TRUE
2              | 2013-02-07  | VACATION    | TRUE

I also have a date table, CALENDARDAY loaded with all dates in the calendar and related information like week numbers, months etc. to help me with the date population.

My attempt at this Query have resulted in the following code:

SELECT unpvt.EMPLOYEENUMBER, unpvt.FIRSTORLAST, unpvt.ABSENCEDATE, unpvt.FIRSTABSENCE, 
unpvt.LASTABSENCE, unpvt.ABSENCETYPE, unpvt.APPROVED, cd.THEDATE, cd.WEEKNUMBER,
    (SELECT TOP 1 EMPLOYEENUMBER
    FROM dbo.ABSENCECALENDARLINE asq
    WHERE cd.THEDATE BETWEEN asq.FIRSTDAYOFABSENCE AND asq.LASTDAYOFABSENCE
    ORDER BY cd.THEDATE DESC) EMPLOYEENUMBER
FROM 
    (SELECT EMPLOYEENUMBER, FIRSTDAYOFABSENCE, LASTDAYOFABSENCE, FIRSTDAYOFABSENCE AS 
     FIRSTABSENCE, LASTDAYOFABSENCE AS LASTABSENCE, ABSENCETYPE, APPROVED
     FROM dbo.ABSENCECALENDARLINE acl) a
UNPIVOT
    (ABSENCEDATE FOR FIRSTORLAST IN 
        (FIRSTDAYOFABSENCE, LASTDAYOFABSENCE)
    ) AS unpvt
RIGHT JOIN dbo.CALENDARDAY cd ON unpvt.ABSENCEDATE = cd.THEDATE
WHERE CAST(THEDATE AS datetime) BETWEEN '2013-01-01' AND '2013-12-31' 
ORDER BY THEDATE

The challenge I meet with this is the SELECT subquery that requires a TOP 1 causing overlapping absences to only return one of the employees absent on a given date. A COUNT on this column returns the number of people absent on that day.

Am I thinking too complicated? How can I easily achieve my desired result? Any help would be greatly appreciated.

Best regards, Alexander

Solution

  • Unless I'm missing something, I think you're over complicating this:

    SELECT a.EMPLOYEENUMBER, b.DATE, a.ABSENCETYPE, a.APPROVED
FROM Table1 a
JOIN Calendar b
   ON b.Date BETWEEN a.FIRSTDAYOFABSENCE AND a.LASTDAYOFABSENCE

    Demo: SQL Fiddle