在上午7点至晚上7点至晚上7点(第二天)之间的数据之和

我有下表：

Date                           Reading1   Reading2
2017-02-15 07:00:00.0000000    33          30
2017-02-15 07:15:00.0000000    32          31
2017-02-15 07:30:00.0000000    32          31
2017-02-15 07:45:00.0000000    33          30
2017-02-15 08:00:00.0000000    33          28
2017-02-15 08:15:00.0000000    32          29
2017-02-15 08:30:00.0000000    32          31
2017-02-15 08:45:00.0000000    34          31
2017-02-15 09:00:00.0000000    34          31
2017-02-15 09:15:00.0000000    34          30
2017-02-15 09:30:00.0000000    31          30
2017-02-15 09:45:00.0000000    32          32
........
2017-02-16 06:15:00.0000000    32          31
2017-02-16 06:30:00.0000000    35          32
2017-02-16 06:45:00.0000000    34          30
2017-02-16 07:00:00.0000000    34          31

我可以根据小时或日期总结dead1和dead2列，但是我的问题是我想在第二天的早上7点至晚上7点之间加入列，然后在第二天的早上7点之间加密列。任何帮助都将是高度明显的。

'总结一下，我正在使用以下查询'

- 总结时

select datepart(hour,Date), SUM(Reading1), SUM(Reading2)
from #LocalTempTable
group by 
    datepart(hour,Date), 
    dateadd(d, 0, datediff(d, 0,Date))

在任何给定的一天，我都会在次级之间进行几个。类似：

declare @refDate datetime
declare @midPeriod datetime
declare @endPeriod datetime
set @refDate = '2017-02-15 07:00'
set @midPeriod = dateadd(hh, 12, @refDate)
set @endPeriod = dateadd(hh, 24, @refDate)
select @refDate PeriodStart, 
       (select sum(Reading1) from #LocalTempTable where Date between @refDate and @midPeriod) EarlyReading1, 
       (select sum(Reading1) from #LocalTempTable where Date between @midPeriod and @endPeriod) LateReading1, 
       (select sum(Reading2) from #LocalTempTable where Date between @refDate and @midPeriod) EarlyReading2, 
       (select sum(Reading2) from #LocalTempTable where Date between @midPeriod and @endPeriod) LateReading2

您可以在小时内使用案例语句进行分组以获取所需的结果：

select year(Date) as DateYear,
    datepart(dy, Date) as DayOfYear,
    case when datepart(hour,Date) >= 7 and datepart(hour, Date) < 19 then '7AM - 7PM' else '7PM - 7AM' end as HourGroup,
    SUM(Reading1),
    SUM(Reading2)
from #LocalTempTable
group by year(Date) as DateYear,
    datepart(dy, Date) as DayOfYear,
    case when datepart(hour,Date) >= 7 and datepart(hour, Date) < 19 then '7AM - 7PM' else '7PM - 7AM' end

这是一个完整的脚本，上面有一些示例数据：

CREATE TABLE #Readings ([Date] DateTime, Reading1 int, Reading2 int)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-15 06:45:00', 1, 1)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-15 07:00:00', 2, 2)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-15 07:15:00', 3, 3)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-15 18:45:00', 4, 4)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-15 19:15:00', 5, 5)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-16 06:45:00', 6, 6)
INSERT INTO #Readings ([Date], Reading1, Reading2) VALUES  ('2017-02-16 07:00:00', 7, 7)
SELECT
    DATEADD(hh, (Half * 12) + 7, ModifiedDay) AS StartPeriod,
    SUM(Reading1) AS SumOfReading1,
    SUM(Reading2) AS SumOfReading2
FROM (
    SELECT
        [Date],
        CAST(CAST(ModifiedDate AS DATE) AS DATETIME) AS [ModifiedDay],
        DATEPART(hh, ModifiedDate) AS [DatePart],
        CASE WHEN DATEPART(hh, ModifiedDate) < 12 THEN 0 ELSE 1 END AS Half,
        Reading1,
        Reading2
    FROM (
        SELECT
            [Date],
            DATEADD(hh, -7, [Date]) AS [ModifiedDate],
            Reading1,
            Reading2
        FROM #Readings      
        ) t
) t
GROUP BY DATEADD(hh, (Half * 12) + 7, ModifiedDay), Half
ORDER BY 1, 2
DROP TABLE #Readings
StartPeriod             SumOfReading1   SumOfReading2
2017-02-14 19:00:00.000 1               1
2017-02-15 07:00:00.000 9               9
2017-02-15 19:00:00.000 11              11
2017-02-16 07:00:00.000 7               7

不使用日历表或cte：

测试设置：http：//rextester.com/zentg4450

select 
    FromDate = convert(varchar(10)
      ,min(dateadd(day,(datediff(hour,0,date)-7)/24,0))
      ,120)
  , ThruDate = convert(varchar(10)
      ,max(dateadd(day,(datediff(hour,0,date)+5)/24,0))
      ,120)
  , Hours = case ((datediff(hour,0,date)+5)/12)%2 
      when 1 
        then '7 AM to 7 PM'
        else '7 PM to 7 AM'
      end
  , SumReading1=sum(Reading1)
  , SumReading2=sum(Reading2) 
from t
group by (datediff(hour,0,date)+5)/12

+------------+------------+--------------+-------------+-------------+
|  FromDate  |  ThruDate  |    Hours     | SumReading1 | SumReading2 |
+------------+------------+--------------+-------------+-------------+
| 2017-02-15 | 2017-02-15 | 7 AM to 7 PM |         392 |         364 |
| 2017-02-15 | 2017-02-16 | 7 PM to 7 AM |         101 |          93 |
| 2017-02-16 | 2017-02-16 | 7 AM to 7 PM |          34 |          31 |
+------------+------------+--------------+-------------+-------------+

使用日历表或CTE：

测试设置：http：//rextester.com/qoc88855

declare @fromdate date = '20170201'
declare @thrudate date = '20170228'
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top ((datediff(day, @fromdate, @thrudate)+1)*2)
      [FromDate]=dateadd(hour,7+12*((row_number() over (order by (select 1)) -1)%2)
                ,convert(datetime2(2)
                  ,dateadd(day, (row_number() over (order by (select 1)) -1)/2, @fromdate))
                        )
    , [ThruDate]=dateadd(hour,19+12*((row_number() over (order by (select 1)) -1)%2)
                ,convert(datetime2(2)
                  ,dateadd(day, (row_number() over (order by (select 1)) -1)/2, @fromdate))
                        )
    from         n as deka
      cross join n as hecto      /* 100 days */
      --cross join n as kilo     /* 2.73 years */
      --cross join n as [tenK]    /* 27.3 years */
   order by 1
)
select 
    FromDate=convert(varchar(20),FromDate,120)
  , ThruDate=convert(varchar(20),ThruDate,120)
  , SumReading1=sum(Reading1)
  , SumReading2=sum(Reading2) 
from dates d
  inner join t
    on t.date >= d.fromdate 
   and t.date <  d.thrudate
group by d.FromDate, d.ThruDate
order by d.FromDate, d.ThruDate

+---------------------+---------------------+-------------+-------------+
|      FromDate       |      ThruDate       | SumReading1 | SumReading2 |
+---------------------+---------------------+-------------+-------------+
| 2017-02-15 07:00:00 | 2017-02-15 19:00:00 |         392 |         364 |
| 2017-02-15 19:00:00 | 2017-02-16 07:00:00 |         101 |          93 |
| 2017-02-16 07:00:00 | 2017-02-16 19:00:00 |          34 |          31 |
+---------------------+---------------------+-------------+-------------+

假设您的[date]列是DATETIME列，您可以做到这一点：（基本上它的作用是将时间范围从上午7点至下午7点分组为一个和7 pm-7 am-les。

select FORMAT(dateadd(hour,-7,[date]), 'yyyy-MM-dd') + case when DATEPART(hour,dateadd(hour,-7,[date])) between 0 and 11 then ' 7AM-7PM' ELSE ' 7PM-7AM' END as [TimeRange], SUM(Reading1), SUM(Reading2)
from #LocalTempTable
group by FORMAT(dateadd(hour,-7,[date]), 'yyyy-MM-dd') + case when DATEPART(hour,dateadd(hour,-7,[date])) between 0 and 11 then ' 7AM-7PM' ELSE ' 7PM-7AM' END

假设：

我们需要每天汇总数据（不计算全天的总和）

我们将准确性考虑到分钟，所以上午7点= 420分钟（从上午0:00）和7pm = 1140分钟

我们将一天分为2组：第1组>今天上午7点，＆lt;今天晚上7点，第2组> =今天下午7点，明天上午7点= 7am（例如，20170228将有2组： 20170228_1和20170228_2）

然后您可以使用以下方式：

SELECT 
    CASE WHEN DATEPART(hh, date)*60 + DATEPART(mi, date) <= 420
        THEN CONVERT(char(8), date - 1, 112) + '_2'
        WHEN DATEPART(hh, date)*60 + DATEPART(mi, date) >= 1140 
        THEN CONVERT(char(8), date, 112) + '_2'
        ELSE CONVERT(char(8), date, 112) + '_1'
    END AS date_group,
    SUM(reading1),
    SUM(reading2) 
FROM table_name
GROUP BY 
    CASE WHEN DATEPART(hh, date)*60 + DATEPART(mi, date) <= 420
        THEN CONVERT(char(8), date - 1, 112) + '_2'
        WHEN DATEPART(hh, date)*60 + DATEPART(mi, date) >= 1140 
        THEN CONVERT(char(8), date, 112) + '_2'
        ELSE CONVERT(char(8), date, 112) + '_1'
    END;

相关内容

最新更新

热门标签：