按DATEPART(hh,TimeStamp)分组以显示夜班，如何查看午夜之后的数据

Question

我正在导入我的数据到excel，所以我需要看到日期作为一个varchar在excel中使用的图形，但我也需要在一天中的个人小时的数据。我的经理每次检查我的图表时都想看到过去一个小时的数据。这是我到目前为止的代码。白班一直很好，但夜班的时间不能超过24小时，所以我不能在excel的图表中对它们进行分组。

convert(VARCHAR, TimeStamp, 101) as date
    ,StationID as lane 
    ,DATEPART(hh,TimeStamp)
        ,.185 as posSD1 
    ,-.185 as negSD1
    ,.370 as posSD2
    ,-.370 as negSD2
    ,.556 as posSD3
    ,-.556 as negSD3

    , COUNT (TrickleActual) as Count
    , convert(decimal (18,3) ,AVG (TrickleActual - TrickleTarget)) as Average

FROM CherryBoxInfo
WHERE TimeStamp >= '2015-05-01'         -- '2015-05-01 18:30:00'
    and  TimeStamp between convert(DATETIME, convert(VARCHAR, TimeStamp, 101) + ' ' + '19:00:00') and convert(DATETIME, convert(VARCHAR, DATEADD(day, 1, TimeStamp), 101) + ' ' + '04:30:00')
    and (TrickleActual-TrickleTarget) BETWEEN -1 and 1

GROUP BY
    convert(VARCHAR, TimeStamp, 101)
    ,StationID
    ,DATEPART(hh,TimeStamp) 

ORDER BY convert(VARCHAR, TimeStamp, 101)
,StationID
,DATEPART(hh,TimeStamp) 

Answer 1

不是按datepart分组，而是按截断为小时的日期分组。

下面是截断日期的方法:

select dateadd(hh, datediff(hh, 0, @dt), 0)