从sql server表中查找周日期、月日期的总计

Question

​

​

我正在设法使今天(星期一)到(上星期一)，星期二到上周，星期二，等等的申请总数。此外，我想得到这个月(到目前为止)的申请。请帮帮忙

我正在使用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

前一周14 : 14-4=10

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

月份(截止日期止总支出)：57

Answer 1

使用LEAD()函数可以实现所需的功能，请参见下面的示例，您可能希望根据需要调整PARTITION BY子句和子查询：

查询结果..

SELECT  T.*, 
        LEAD (AppCount) OVER (PARTITION BY cDay ORDER BY weekNum) as PrevAppCount,
        T.AppCount - LEAD (AppCount) OVER (PARTITION BY cDay ORDER BY weekNum) 
FROM    (
        select  Category, 
                datepart(week,cDate) as weekNum,
                cDay,
                sum (AppCount) as AppCount
        from MyTable
        Group by Category, Datepart(week,cDate),cDay
        ) AS T

Answer 2

您可以这样编写查询：

;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