从不同的行和不同的日期获取最小和最大日期时间

I have following Table StudentEntryDetails:

我有以下表StudentEntryDetails:

Id    StudId             CheckTime              CheckType
1       1      2015 - 01 - 08 09: 00: 00.000       IN
2       1      2015 - 01 - 08 11: 00: 00.000       OUT
3       1      2015 - 01 - 08 13: 00: 00.000       IN
4       1      2015 - 01 - 08 19: 00: 00.000       OUT
5       1      2015 - 01 - 09 08: 00: 00.000       IN
6       1      2015 - 01 - 09 12: 00: 00.000       OUT
7       1      2015 - 01 - 09 13: 00: 00.000       IN
8       1      2015 - 01 - 09 14: 00: 00.000       OUT
9       1      2015 - 01 - 10 08: 00: 00.000       IN
10      1      2015 - 01 - 10 15: 00: 00.000       OUT
11      2      2015 - 01 - 08 10: 00: 00.000       IN
12      2      2015 - 01 - 08 13: 00: 00.000       OUT
13      2      2015 - 01 - 13 07: 00: 00.000       IN
14      2      2015 - 01 - 13 13: 00: 00.000       OUT
15      2      2015 - 01 - 13 14: 00: 00.000       IN
16      2      2015 - 01 - 13 17: 00: 00.000       OUT
17      3      2015 - 01 - 09 12: 00: 00.000       IN
18      3      2015 - 01 - 09 14: 00: 00.000       OUT

I want Min & Max Time for each date and for each user in table in a same row I have written following code

我希望每个日期的最小和最大时间以及表格中的每个用户都在我编写的代码中

select StudId, Convert(date,CheckTime),
    CheckInTime=min(case when CheckType='IN' then CheckTime end),
    CheckOutTime=max(case when CheckType='OUT' then CheckTime end)
from StudentEntryDetails group by StudId,CheckTime

but it gives improper result. I want following result.

但它给出了不正确的结果。我想要以下结果。

StudId     Date             CheckInTime             CheckOutTime
1        2015-01-08    2015-01-08 09:00:00.000   2015-01-08 19:00:00.000
1        2015-01-09    2015-01-09 08:00:00.000   2015-01-09 14:00:00.000
1        2015-01-10    2015-01-10 08:00:00.000   2015-01-10 15:00:00.000
3        2015-01-09    2015-01-09 12:00:00.000   2015-01-09 14:00:00.000
2        2015-01-08    2015-01-08 10:00:00.000   2015-01-08 13:00:00.000
2        2015-01-13    2015-01-13 07:00:00.000   2015-01-13 17:00:00.000

1 个解决方案

#1

you need to GROUP BY StudId,Convert(date,CheckTime). Something like this

你需要GROUP BY StudId,Convert(date,CheckTime)。像这样的东西

SELECT StudId, CONVERT(DATE,CheckTime),
    CheckInTime = MIN(CASE WHEN CheckType='IN' THEN CheckTime END),
    CheckOutTime = MAX(CASE WHEN CheckType='OUT' THEN CheckTime END)
FROM StudentEntryDetails
GROUP BY StudId,CONVERT(DATE,CheckTime)