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
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)
#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)