I have a table like this:
| user | date | time |
| ------------ | ------------ | ------------ |
| 01 | 2023-02-28 | 08:00 |
| 01 | 2023-02-28 | 09:15 |
| 01 | 2023-02-28 | 04:20 |
| 01 | 2023-02-27 | 19:00 |
I want to select first and last time for each user per day. But the day is not start from 0:00 to 24:00 but 5:00 to 5:00.
So the expected query result of above data should be
| user | date | firsttime | lasttime |
| ------------ | ------------ | ------------ | ------------ |
| 01 | 2023-02-28 | 2023-02-28 08:00 | 2023-02-28 09:15 |
| 01 | 2023-02-27 | 2023-02-27 19:00 | 2023-02-27 04:20 |
How to do that? I think I need to first find which date the time belong to,like
select
user,
convert(datetime, date + ' ' + time, 121) as time,
case
when convert(datetime, date + ' ' + time, 121) > convert(datetime, date + ' ' + "05:00:00", 121)
then convert(datetime, date, 101)
when convert(datetime, date + ' ' + time, 121) <= convert(datetime, date + ' ' + "05:00:00", 121)
then convert(datetime, date, 101) - 1
end as Belongdate
from
Table
Then I group the result set by user and belongdate to get the result. Is there any other solution? Thanks
3条答案
按热度按时间4jb9z9bj1#
If the columns
date
andtime
are of typesdate
andtime
:If both columns are
datetime
:If you stored a single value in a single datetime column (as you should):
All three examples are demonstrated in this db<>fiddle .
ruyhziif2#
Edit: This solution assumes date & time are of date and time datatypes, respectively. @Aaron Bertrand made no such assumption so that's probably the way to go. Also, this assumes you want the date, for the records where time < 5:00, to be updated to previous day (unsure if it's accurate, but it's what shows in the expected)
plicqrtu3#