代码之家 › 专栏 › 技术社区 › Doonie Darkoo

获取所提供日期之间的所有日期

sql-server sql

Doonie Darkoo · 技术社区 · 7 年前

CREATE TABLE Attendance
(
    [EmpCode] int,
    [TimeIn] datetime,
    [TimeOut] datetime
)

INSERT INTO Attendance VALUES (12, '2018-08-01 09:00:00', '2018-08-01 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-02 09:00:00', '2018-08-02 18:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-03 09:25:00', '2018-08-03 16:56:00');
INSERT INTO Attendance VALUES (12, '2018-08-04 09:13:00', '2018-08-05 18:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-06 09:00:00', '2018-08-07 18:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-07 09:27:00', '2018-08-08 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-08 09:35:00', '2018-08-09 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-10 09:00:00', '2018-08-10 17:45:00');
INSERT INTO Attendance VALUES (12, '2018-08-11 09:50:00', '2018-08-11 17:31:00');
INSERT INTO Attendance VALUES (12, '2018-08-13 09:23:00', '2018-08-13 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-15 09:21:00', '2018-08-15 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-16 09:00:00', '2018-08-16 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-17 09:34:00', '2018-08-17 17:29:00');
INSERT INTO Attendance VALUES (12, '2018-08-18 09:00:00', '2018-08-18 17:10:00');
INSERT INTO Attendance VALUES (12, '2018-08-20 09:34:00', '2018-08-20 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-21 09:20:00', '2018-08-21 17:15:00');
INSERT INTO Attendance VALUES (12, '2018-08-22 09:12:00', '2018-08-22 17:19:00');
INSERT INTO Attendance VALUES (12, '2018-08-23 09:05:00', '2018-08-23 17:21:00');
INSERT INTO Attendance VALUES (12, '2018-08-24 09:07:00', '2018-08-24 17:09:00');
INSERT INTO Attendance VALUES (12, '2018-08-25 09:12:00', '2018-08-25 17:05:00');
INSERT INTO Attendance VALUES (12, '2018-08-27 09:21:00', '2018-08-27 17:46:00');
INSERT INTO Attendance VALUES (12, '2018-08-28 09:17:00', '2018-08-28 17:12:00');
INSERT INTO Attendance VALUES (12, '2018-08-29 09:00:00', '2018-08-29 17:36:00');
INSERT INTO Attendance VALUES (12, '2018-08-30 09:12:00', '2018-08-30 17:24:00');

我有一个查询,告诉员工工作了多少小时,但它只显示了表中存在数据的天数。我想显示提供的日期之间的所有日期,如果没有数据,它应该在列中为空。

以下是查询:

SELECT
    [EmpCode],
    FirstIN    = CAST(MIN([TimeIn]) AS TIME), 
    LastOUT    = CAST(MAX([TimeOut]) AS TIME), 
    CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME))/3600) 
       + ':'
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 60) , 2 ) AS HoursSpent,
    CAST(COALESCE(TimeIn, TimeOut) AS DATE) [Date]    
FROM Attendance
WHERE CAST(COALESCE(TimeIn, TimeOut) AS DATE) BETWEEN '2018-08-01' AND '2018-08-25'    
GROUP BY EmpCode, TimeIn, TimeOut

3 回复 | 直到 7 年前

Yogesh Sharma 7 年前

为此,您需要使用递归方式生成可能的日期:

with t as (
     select '2018-08-01' as startdt
     union all
     select dateadd(day, 1, startdt)
     from t
     where startdt < '2018-08-25'
)

select . . . 
from t left join
     Attendance at
     on cast(coalesce(at.TimeIn, at.TimeOut) as date) = t.startdt;

一定要用“开始日期” t 而不是 Attendance 中的表 SELECT 声明。

注意:如果您有一个大的日期周期号,那么不要忘记使用查询提示 OPTION (MAXRECURSION 0) 100 递归级别。

Jayasurya Satheesh 7 年前

DECLARE @From DATETIME = '2018-08-01' ,@To DATETIME= '2018-08-25'

;WITH CTE
AS
(
    SELECT
        [EmpCode] EmpId,
        MyDate = @From
        FROM Attendance A
    UNION ALL
    SELECT
        EmpId,
        MyDate = DATEADD(DAY,1,MyDate)
        FROM CTE
            WHERE MyDate < @To
)
SELECT
[EmpCode] = CTE.EmpId,
CTE.MyDate,
FirstIN    = CAST(MIN([TimeIn]) AS TIME), 
LastOUT    = CAST(MAX([TimeOut]) AS TIME), 
CONVERT(VARCHAR(6), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME))/3600) 
       + ':'
       + RIGHT('0' + CONVERT(VARCHAR(2), (Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 3600) / 60), 2) 
       + ':' 
       + RIGHT('0' + CONVERT(VARCHAR(2), Datediff(second, CAST(MIN([TimeIn]) AS TIME), CAST(MAX([TimeOut]) AS TIME)) % 60) , 2 ) 

       AS HoursSpent,
CAST(CTE.MyDate AS DATE) [Date]

FROM CTE
    LEFT JOIN Attendance A
        ON A.EmpCode = CTE.EmpId
            AND CAST(CTE.MyDate AS DATE) = CAST(COALESCE(TimeIn, TimeOut) AS DATE)
GROUP BY CTE.EmpId, TimeIn, TimeOut,CTE.MyDate
    ORDER BY 6

Thom A 7 年前

另一种方法,使用计数表。这里的优点是rCTE是RBAR的一种形式。理货台的想法不是那么明显,但是更快,而且也不需要 OPTION (MAXRECURSION 0) 如果您有超过100天的时间,请添加。事实上,本例最多处理10000天,这就足够了:

DECLARE @EmpCode int = 12;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000
),
Dates AS(
    SELECT DATEADD(DAY, T.I, TT.MinTimeIn) AS CalendarDate,
           @EmpCode AS EmpCode
    FROM Tally T
         CROSS APPLY (SELECT MIN(CONVERT(date,TimeIn)) AS MinTimeIn,
                             MAX(CONVERT(date,TimeOut)) AS MaxTimeOut
                      FROM Attendance
                      WHERE EmpCode = @EmpCode) TT
     WHERE DATEADD(DAY, T.I, TT.MinTimeIn) <= CONVERT(date, TT.MaxTimeOut))
SELECT CalendarDate
       EmpCode,
       TimeIn,
       TimeOut
FROM Dates D
     LEFT JOIN Attendance A ON D.CalendarDate = CONVERT(date,A.TimeIn)
                           AND D.EmpCode = A.EmpCode;