时间戳

首页 > 解决方案 > 显示每周总计

sql - 显示每周总计

问题描述

我正在尝试创建一个报告，显示一周内使用的总时间。我只成功地使用以下公式显示了一天使用的总数：

select left(report_date, 11) as week_start,
       case
         when id is null then
          'Enterprise'
         else
          id
       end id,
       case
         when id in ('CN','NQ','AR','DA','PR','FM') then
          'West'
         when id = 'Enterprise' then
          'ALL'
         else
          'East'
       end as Region,
       sum(actual_seconds) / 3600 as Actual_Hours,
       sum(goal_seconds) / 3600 as Goal_Hours,
       sum(goal_seconds * 1.0) / nullif(sum(actual_seconds * 1.0), 0) * 100 as Performance
  from summary_table
 where report_date > '2019-01-01 00:00:00.000'
 group by rollup(id), report_date
 order by report_date

如果可能的话，我还想有“东”和“西”的总数。

任何帮助将不胜感激，在此先感谢您！

标签： sqlsql-server

解决方案

我能够按周对其进行汇总，但仍然遇到按地区添加总数的问题。

Select
    dateadd(day, -(datepart(weekday, report_date) + 6 ) % 7, report_date) as week_start, 
   case
         when id is null then
          'Enterprise'
         else
          id
       end id,
       case
         when id in ('CN','NQ','AR','DA','PR','FM') then
          'West'
         when id = 'Enterprise' then
          'ALL'
         else
          'East'
       end as Region,  
        sum(actual_seconds)/3600.0 as Actual_Hours,
        sum(goal_seconds)/3600.0 as Goal_Hours,
        sum(goal_seconds*1.0)/NULLIF(sum(actual_seconds*1.0),0)*100 as performance 
    from     summary_table
    where        dateadd(day, -(datepart(weekday, report_date) + 6 ) % 7, report_date) >= 
                      dateadd(day, -365 - datepart(dw, getdate()), getdate())
    group by    dateadd(day, -(datepart(weekday, report_date) + 6 ) % 7, report_date), rollup(id)

推荐阅读