我怎样才能实现这个SQL查询？

How can I output the sample Record, that looks like in the Expected Result?

如何输出样本记录,看起来像预期结果?

Sample Database

 Table Sample1
 Column Status      Int
 Column dtDateTime  dateTime
 Column User        varchar(50)

Records

 User     dtDateTime                   Status
 User1    2017-05-19 08:00:00.000      1
 User1    2017-05-19 17:05:00.000      2
 User1    2017-05-20 09:05:00.000      1
 User1    2017-05-21 17:35:00.000      2

The Record 1 as Time-IN and the Record 2 as Time-Out on the column status

记录1为Time-IN,记录2为列状态的超时

Expected Result

 User         WORK-DATE      Time-IN         Time-OUT       Status
 user1        2017-05-19     08:00:00.000    17:05:00.000   COMPLETE
 User1        2017-05-20     09:05:00.000    NULL           NO OUT
 User1        2017-05-21     NULL            17:35:00.000   NO IN

1 个解决方案

#1

My crystal ball suggested this query:

我的水晶球建议这个查询:

SELECT   User, 
         CONVERT(DATE, dtDateTime) AS [WORK-DATE],
         CASE WHEN MIN(STATUS) <> MAX(STATUS) THEN 'COMPLETE'
              WHEN MAX(STATUS) = 1 THEN 'NO OUT'
              WHEN MIN(STATUS) = 2 THEN 'NO IN'
              END           
FROM     Sample1
GROUP BY User, CONVERT(DATE, dtDateTime)

EDIT : the crystal ball didn't show the TIME column earlier

编辑:水晶球早先没有显示TIME栏

SELECT   User, 
         CONVERT(DATE, dtDateTime) AS [WORK-DATE],
         MAX(CASE WHEN STATUS = 1 THEN CONVERT(TIME, dtDateTime) END) AS [TIME-IN],
         MAX(CASE WHEN STATUS = 2 THEN CONVERT(TIME, dtDateTime) END) AS [TIME-OUT],
         CASE WHEN MIN(STATUS) <> MAX(STATUS) THEN 'COMPLETE'
              WHEN MAX(STATUS) = 1 THEN 'NO OUT'
              WHEN MIN(STATUS) = 2 THEN 'NO IN'
              END AS Status           
FROM     Sample1
GROUP BY User, CONVERT(DATE, dtDateTime)

#1