如何转动两个日期列，并填写多个重叠的日期周期的差距 [英] How to pivot two date columns and fill in the gaps with multiple overlapping date periods

问题描述

我有一张带有员工缺勤条目的表格。行包含员工编号，缺勤的第一天和最后一天以及更多的数据，如缺勤类型，批准等。

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

我想创建一个视图，缺省条目列出所有日期。这样的东西。

 所需结果：
 
 EMPLOYEENUMBER | ABSENCEDATE | ABSENCETYPE | APPROVED 
 --------------- + ------------- + ------------- +  - --------- 
 1 | 2013-01-01 |假期| TRUE 
 1 | 2013-01-02 |假期| TRUE 
 1 | 2013-01-03 |假期| TRUE 
 1 | 2013-01-04 |假期| TRUE 
 2 | 2013-01-01 |假期| TRUE 
 2 | 2013-01-02 |假期| TRUE 
 3 | 2013-02-05 |假期| TRUE 
 .. .. .. .. 
 3 | 2013-02-08 |假期| TRUE 
 2 | 2013-02-06 |假期| TRUE 
 2 | 2013-02-07 |假期| TRUE 
  

我还有一个日期表， CALENDARDAY 加载日历中的所有日期和相关信息，如周数，月份等，以帮助我填写日期人口。

我在此查询中的尝试导致以下代码：

  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 
订单由cd.THEDATE DESC）EMPLOYEENUMBER 
从
（SELECT EMPLOYEENUMBER，FIRSTDAYOFABSENCE，LASTDAYOFABSENCE，FIRSTDAYOFABSENCE AS 
 FIRSTABSENCE，LASTDAYOFABSENCE AS LASTABSENCE，ABSENCETYPE，APPROVED 
从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（以datetime为准）BETWEEN'2013-01-01'AND '2013-12-31'
 ORDER BY THEDATE 
  

我遇到的挑战是需要 TOP 1 的 SELECT 子查询导致重叠缺席，只返回给定日期缺少的员工之一。此列上的 COUNT 会返回当天缺席的人数。

我觉得太复杂了吗？如何轻松实现我所期望的结果？任何帮助将不胜感激。

最好的问候，
亚历山大

解决方案

除非我缺少某些东西，否则我认为你已经复杂化了：

  SELECT a.EMPLOYEENUMBER， b.DATE，a.ABSENCETYPE，a.APPROVED 
 FROM Table1 a 
 JOIN日历b 
 ON b.Date BETWEEN a.FIRSTDAYOFABSENCE AND a.LASTDAYOFABSENCE 
  

演示： SQL小提琴

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

解决方案

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

这篇关于如何转动两个日期列，并填写多个重叠的日期周期的差距的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！