要使用的SQL语法

Question

我需要关于如何输出样本的帮助，因为我知道这是不可能的。

示例表

表: employee_dtrlogs

 ID IDENTITY(1,1) PRIMARY
 empuser Char(10)
 dtDateTime DateTime
 dtstatus Int

记录：

 ID      empuser  empcode     dtDateTime          dtStatus
 1       USER1    USR1        2017-7-1 09:00:00    1
 2       USER1    USR1        2017-7-1 18:00:00    2
 3       USER1    USR1        2017-7-4 09:00:00    1
 4       USER1    USR1        2017-7-4 18:00:00    2

表: employee_calendar

 CalendarDate Date

记录：

 CaledarDate
 2017-7-1
 2017-7-2
 2017-7-3
 2017-7-4
 2017-7-5

预期输出

 ID     USER     EMPCODE DATE      TIME     STATUS
 1      USER1    USR1    2017-7-1  09:00:00 TIME-IN
 2      USER1    USR1    2017-7-1  18:00:00 TIME-OUT
 3      USER1    USR1    2017-7-2  NULL     NULL
 4      USER1    USR1    2017-7-2  NULL     NULL
 5      USER1    USR1    2017-7-3  NULL     NULL
 6      USER1    USR1    2017-7-3  NULL     NULL
 7      USER1    USR1    2017-7-4  09:00:00 TIME-IN
 8      USER1    USR1    2017-7-4  18:00:00 TIME-OUT
 9      USER1    USR1    2017-7-5  NULL     NULL
 10     USER1    USR1    2017-7-5  NULL     NULL

Answer 1

你能试试这个吗？

DECLARE @employee_dtrlogs TABLE ( ID INT, empuser VARCHAR(20), empcode VARCHAR(20), dtDateTime DATETIME, dtStatus INT)
INSERT INTO @employee_dtrlogs
VALUES
 (1,'USER1','USR1','2017-7-1 9:00:00',1),
 (2,'USER1','USR1','2017-7-1 18:00:00',2),
 (3,'USER1','USR1','2017-7-4 9:00:00',1),
 (4,'USER1','USR1','2017-7-4 18:00:00',2)

DECLARE @employee_calendar TABLE( CalendarDate DATE)

INSERT INTO @employee_calendar  VALUES
 ('2017-7-1'),
 ('2017-7-2'),
 ('2017-7-3'),
 ('2017-7-4'),
 ('2017-7-5')


;WITH UserAndStatus AS (
    SELECT 
        DISTINCT empuser, empcode, dtStatus 
    FROM @employee_dtrlogs 
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY C.CalendarDate , U.dtStatus) ID,
    U.empuser [USER],
    U.empcode EMPCODE,
    C.CalendarDate [DATE],
    CONVERT(VARCHAR, L.dtDateTime, 14) TIME,
    CASE L.dtStatus WHEN 1 THEN 'TIME-IN' WHEN 2 THEN 'TIME-OUT' END STATUS
FROM @employee_calendar C  
    CROSS JOIN UserAndStatus U
    LEFT JOIN @employee_dtrlogs L ON 
            L.empcode = U.empcode
            AND L.dtStatus = U.dtStatus
            AND C.CalendarDate = CAST(L.dtDateTime AS date)

结果

ID                   USER                 EMPCODE              DATE       TIME                           STATUS
-------------------- -------------------- -------------------- ---------- ------------------------------ --------
1                    USER1                USR1                 2017-07-01 09:00:00:000                   TIME-IN
2                    USER1                USR1                 2017-07-01 18:00:00:000                   TIME-OUT
3                    USER1                USR1                 2017-07-02 NULL                           NULL
4                    USER1                USR1                 2017-07-02 NULL                           NULL
5                    USER1                USR1                 2017-07-03 NULL                           NULL
6                    USER1                USR1                 2017-07-03 NULL                           NULL
7                    USER1                USR1                 2017-07-04 09:00:00:000                   TIME-IN
8                    USER1                USR1                 2017-07-04 18:00:00:000                   TIME-OUT
9                    USER1                USR1                 2017-07-05 NULL                           NULL
10                   USER1                USR1                 2017-07-05 NULL                           NULL

Answer 2

这是一种实现输出的方法

    SELECT l.ID, l."DATE", l."USER", c.CaledarDate, l.STATUS
    FROM employee_dtrlogs  l 
      RIGHT JOIN employee_calendar c
        ON  c.CaledarDate=l.DATE AND l.STATUS='TIME-IN'
UNION ALL
    SELECT l.ID, l."DATE", l."USER", c.CaledarDate, l.STATUS
    FROM employee_dtrlogs  l 
      RIGHT JOIN employee_calendar c
        ON  c.CaledarDate=l.DATE AND l.STATUS='TIME-OUT'
ORDER BY  c.CaledarDate, l.ID

SQLfiddle

Answer 3

试试这个：

select distinct edr.ID,c.[User], c.[Date], edr.[Time], c.[Status]  from  employee_dtrlogs edr RIGHT JOIN 
(
select * from (select distinct [User], [Status] FROM employee_dtrlogs)a  
CROSS JOIN (SELECT [Date] FROM employee_calendar) b

)c
on edr.[USER] = c.[User] AND edr.[Date] = c.[Date] AND edr.[Status] = c.[Status]
ORDER BY c.[Date]

请注意，我使用Date作为列名，而不是CalendarDate。您可以替换它并检查输出