SQL for Weekly &月度总计

Question

我试图效仿一个类似的例子，从：StackOverFlow: SQL Add Sum Row for Week and At the End Add the Grand Total获得每周和每月总计数据。这个级别的SQL对我来说是一个扩展，所以请尽可能清楚。

我有一张桌子，上面有超过200K的ID，Store_ID，Sales_Date，Amount记录。

ID  Store_ID    Amount  Sales_Date
1   215            7    1/29/2012
2   215            7    1/30/2012
3   215            7    1/31/2012
4   215            7    2/1/2012
5   215            7    2/2/2012
6   215            7    2/3/2012
7   215            7    2/4/2012
8   215            8    2/5/2012
9   215            8    2/6/2012
10  215            8    2/7/2012
    ***More and More Data***        
162 218            4    10/30/2011
163 218            4    10/31/2011
164 218            4    11/1/2011
165 218            4    11/2/2011
166 218            4    11/3/2011
167 218            4    11/4/2011
168 218            4    11/5/2011
169 218            8    11/6/2011
170 218            8    11/7/2011
171 218            8    11/8/2011
           ******LOTS MORE DATA*****

我需要生成一个视图，该视图将显示每个Store_ID的每周和每月总计以及相关的总计日期。我遇到的问题是，该示例为我提供了周总计(没有日期相关)、月总计(没有日期关联)和每日金额(这是一个附带福利)。

我需要知道如何添加一个额外的列的结果，将显示我的周结束日期和月结束日期。

到目前为止，这就是我所拥有的(它几乎和这个例子完全一样)：

set datefirst 7

select top 100
    case
        when grouping(cast(datepart(week, [Sales_Date]) as varchar(255)))=1 then '<MonthEnd>'
        when grouping(cast([Sales_Date] as date))=1 then '<weektotal>'
        else cast(cast([Sales_Date] as date) as varchar(255))
    end as Period
    , WkSales = sum(Amount)
    , Store = Store_ID
From KF_Store_Sales_Daily

group by 
    grouping sets(  
    (cast(datepart(month, [Sales_Date]) as varchar(255)), cast(datepart(week, [Sales_Date]) as varchar(255)),cast([Sales_Date] as date)),
    (cast(datepart(month, [Sales_Date]) as varchar(255)), cast(datepart(week, [Sales_Date]) as varchar(255))),
    (cast(datepart(month, [Sales_Date]) as varchar(255)))
    )
    , Store_ID
ORDER BY Store_ID, Sales_Date    

Answer 1

以下查询可用于显示每日、每周、每月和每年的总数：

select
    case
        when grouping(d.m)=1 then 'Year ' + cast(max(d.y) as varchar(10))
        when grouping(d.w)=1 then datename(m, max(Sales_Date)) + ' ' + cast(max(d.y) as varchar(10))
        when grouping(Sales_Date)=1 then 'Week ' + datename(m, max(ws)) + ' ' + cast(datepart(d, max(ws)) as varchar(20)) + ' - '
            + datename(m, max(we)) + ' ' + cast(datepart(d, max(we)) as varchar(20))
        else cast(cast([Sales_Date] as date) as varchar(255))
    end as Period
    , Sales = sum(Amount)
    , Store = Store_ID
from KF_Store_Sales_Daily
    cross apply (
        select -- aux. expressions for dates
            datepart(yy, [Sales_Date]), -- year
            datepart(m, [Sales_Date]), -- month
            datepart(wk, [Sales_Date]), -- week
            dateadd(d, 1-datepart(w, Sales_date), Sales_date), -- week start
            dateadd(d, 7-datepart(w, Sales_date), Sales_date) -- week end
    ) d(y, m, w, ws, we)
group by Store_ID, d.y, rollup (d.m, d.w, Sales_Date)
order by d.y desc,
    grouping(d.m), d.m,
    grouping(d.w), d.w,
    grouping(Sales_Date), Sales_Date

我不知道把每月和每周的总数放在一起有多方便(因为一周可能属于两个月)。如果您需要这两个查询，请执行以下两个查询。

每日、每月和每年合计：

select
    case
        when grouping(d.m)=1 then 'Year ' + cast(max(d.y) as varchar(10))
        when grouping(Sales_Date)=1 then datename(m, max(Sales_Date)) + ' ' + cast(max(d.y) as varchar(10))
        else cast(cast([Sales_Date] as date) as varchar(255))
    end as Period
    , Sales = sum(Amount)
    , Store = Store_ID
from KF_Store_Sales_Daily
    cross apply (
        select
            datepart(yy, [Sales_Date]),
            datepart(m, [Sales_Date])
    ) d(y, m)
group by Store_ID, d.y, rollup (d.m, Sales_Date)
order by d.y desc,
    grouping(d.m), d.m,
    grouping(Sales_Date), Sales_Date

就每日、每周和每年合计而言(在这种情况下，一周可能属于两年)：

select
    case
        when grouping(d.w)=1 then 'Year ' + cast(max(d.y) as varchar(10))
        when grouping(Sales_Date)=1 then 'Week ' + datename(m, max(ws)) + ' ' + cast(datepart(d, max(ws)) as varchar(20)) + ' - '
            + datename(m, max(we)) + ' ' + cast(datepart(d, max(we)) as varchar(20))
        else cast(cast([Sales_Date] as date) as varchar(255))
    end as Period
    , Sales = sum(Amount)
    , Store = Store_ID
from KF_Store_Sales_Daily
    cross apply (
        select
            datepart(yy, [Sales_Date]),
            datepart(wk, [Sales_Date]),
            dateadd(d, 1-datepart(w, Sales_date), Sales_date),
            dateadd(d, 7-datepart(w, Sales_date), Sales_date)
    ) d(y, w, ws, we)
group by Store_ID, d.y, rollup (d.w, Sales_Date)
order by d.y desc,
    grouping(d.w), d.w,
    grouping(Sales_Date), Sales_Date