时间戳

首页 > 解决方案 > 从 sql server 表中查找 weektodate、monthtodate 的总计

sql-server - 从 sql server 表中查找 weektodate、monthtodate 的总计

问题描述

在此处输入图像描述

我正在尝试获取当天 (周一) 到 (上周一) 和周二到上周二的申请总数，依此类推。另外，我想获得本月（本月至今）的申请。请帮忙

我正在使用 SQL Server 2008 R2

Category    Date    Day     Applications
CASS    16/09/2019  Monday  1
CASS    16/09/2019  Monday  3
RBS     16/09/2019  Monday  1
RBS     16/09/2019  Monday  3
RBS     16/09/2019  Monday  14
RBS     16/09/2019  Monday  15
CASS    23/09/2019  Monday  2
CASS    23/09/2019  Monday  1
CASS    23/09/2019  Monday  2
CASS    23/09/2019  Monday  1
CASS    23/09/2019  Monday  8
RBS     23/09/2019  Monday  3
RBS     23/09/2019  Monday  3

输出：

               23/09/2019

CASS：14 前一周：14-4=10

苏格兰皇家银行：6 前一周：6-33= -27

本月至今（截至日期的总申请）：57

标签： sql-server

解决方案

你可以这样写查询：

;with cte as (
select *, rown = ROW_NUMBER() over(partition by category order by [Date]) from (
    select category, [Date], sum(applications) appcnt from #table 
        group by category, [Date]
    ) t
)
Select  case when nextweek.Category is null then curweek.Category else 
 curweek.Category + ' Previous week' end , 
    case when nextweek.Category is not null then nextweek.appcnt - curweek.appcnt   else curweek.appcnt end
    from cte curweek 
  left join cte nextweek on curweek.rown = nextweek.rown -1
    and curweek.Category = nextweek.Category
union all
  select 'Month to date', sum(applications) from #table

代码：

https://rextester.com/ABI84558

推荐阅读