0

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

Alexander
  • 3
  • 2

1 Answers1

0

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

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • You are 100 % correct. I was thinking way to complicated. I guess it was the `pivot` oportunity that just stlole my focus and made it much harder to solve! Thank you very much for putting my feet back on the ground. – Alexander Jul 18 '13 at 18:12
  • I definitely do that sometimes, fixate on a technique and try to jam it into places where it has no business. – Hart CO Jul 18 '13 at 18:15