How to group by data by a 24 hours interval start from 5:00 in SQL Server?

olhwl3o2 于 2023-03-28 发布在 SQL Server

关注(0)|答案(3)|浏览(156)

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

sql-server

来源：https://stackoverflow.com/questions/75859126/how-to-group-by-data-by-a-24-hours-interval-start-from-500-in-sql-server

3条答案

按热度按时间

4jb9z9bj1#

If the columns date and time are of types date and time :

SELECT [user], 
       date = effdate, 
       firsttime = MIN(dt), 
       lasttime  = MAX(dt)
  FROM 
  (
    SELECT [user], 
      effdate = dateadd(DAY, case 
        WHEN DATEPART(HOUR, time) < 5 then -1 ELSE 0 END, date),
      dt = dateadd(minute, datediff(minute, 0, time), convert(datetime, date))
    FROM dbo.[TableA]
  ) AS agg
  GROUP BY [user], effdate;

If both columns are datetime :

SELECT [user], 
       date = CONVERT(date, effdate), 
       firsttime = MIN(dt), 
       lasttime  = MAX(dt)
  FROM 
  (
    SELECT [user], 
      effdate = dateadd(DAY, case 
        WHEN DATEPART(HOUR, time) < 5 then -1 ELSE 0 END, date),
      dt = dateadd(minute, datediff(minute, 0, time), date)
    FROM dbo.[TableB]
  ) AS agg
  GROUP BY [user], effdate;

If you stored a single value in a single datetime column (as you should):

SELECT [user], 
  date = CONVERT(date, effdate), 
  firsttime = MIN(dt), 
  lasttime  = MAX(dt)
FROM 
(
  SELECT [user], dt, 
    effdate = dateadd(DAY, case 
      WHEN DATEPART(HOUR, dt) < 5 then -1 ELSE 0 END, dt)
  FROM dbo.[TableC]
) AS agg
GROUP BY [user], CONVERT(date, effdate);

All three examples are demonstrated in this db<>fiddle .

赞(0）回复(0）举报 2023-03-28

ruyhziif2#

DECLARE @Data TABLE
    (
        [id] INT
      , [dd] DATE
      , [tm] TIME
    ) ;
INSERT INTO @Data
VALUES
    ( 01, '2023-02-28', '08:00' )
  , ( 01, '2023-02-28', '09:15' )
  , ( 01, '2023-02-28', '04:20' )
  , ( 01, '2023-02-27', '19:00' ) ;

DECLARE @StartofDd TIME = '05:00' ;

;WITH CTE AS
    (
        SELECT  [id]
              , [dd]
              , [tm]
              , IIF([tm] < @StartofDd, DATEADD ( dd, -1, [dd] ), [dd]) AS [ndd]
              , SUM ( IIF([dd] != IIF([tm] < @StartofDd, DATEADD ( dd, -1, [dd] ), [dd]), 1, 0)) OVER ( PARTITION BY IIF([tm] < @StartofDd, DATEADD ( dd, -1, [dd] ), [dd])) AS [chk]
        FROM    @Data
    )
SELECT      [id]
          , [ndd]
          , IIF(MIN ( [chk] ) = 0, CAST (MIN ( [ndd] ) AS DATETIME) + CAST (MIN ( [tm] ) AS DATETIME), CAST (MIN ( [ndd] ) AS DATETIME) + CAST (MAX ( [tm] ) AS DATETIME)) AS [firsttime]
          , IIF(MIN ( [chk] ) = 0, CAST (MAX ( [ndd] ) AS DATETIME) + CAST (MAX ( [tm] ) AS DATETIME), CAST (MAX ( [ndd] ) AS DATETIME) + CAST (MIN ( [tm] ) AS DATETIME)) AS [lasttime]
FROM        CTE
GROUP BY    [id]
          , [ndd] ;

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)

赞(0）回复(0）举报 2023-03-28

plicqrtu3#

convert date and time into datetime
substract 5:00 hours every time to normalize
convert to date_normalized
group by date_normalized

SELECT
     CAST(CONVERT(DATETIME,date_value+' '+time_value,121)-CONVERT(DATETIME,'5:00') AS DATE)
       AS date_normalized,

赞(0）回复(0）举报 2023-03-28

我来回答

How to group by data by a 24 hours interval start from 5:00 in SQL Server?

3条答案

相关问题

热门标签

最新问答