如何在sql中按每周到最后六个星期的星期日日期分组?
目前正在编写报告.我需要的是样本表，
实例类型 Sep-23 Sep-16 Sep-09 Sep-02 Aug-26 Aug-19------------------------------------------------------------------------早期 ASN 4 2 4 1 1 2延迟 ASN 2 1 5 3 1 1总和 6 3 9 4 2 3但实际表是，
SPGI01_INSTANCE_TYPE_C SPGI01_CREATE_S--------------------------------------------------------------早期 ASN 9/17/2012 12:00:00.000早期 ASN 9/18/2012 10:06:11.000早期 ASN 9/19/2012 8:00:04.000早期 ASN 9/20/2012 3:00:05.000早期 ASN 9/10/2012 12:00:07.000早期 ASN 9/11/2012 12:00:32.000早期 ASN 9/3/2012 12:00:17.000早期 ASN 9/4/2012 10:06:00.000早期 ASN 9/5/2012 8:00:00.000早期 ASN 9/6/2012 3:00:00.000早期 ASN 8/31/2012 12:00:00.000早期 ASN 8/26/2012 12:00:00.000早期 ASN 8/14/2012 12:00:00.000早期 ASN 8/15/2012 12:00:00.000晚 ASN 9/17/2012 12:00:00.000晚 ASN 9/18/2012 10:06:00.000晚 ASN 9/11/2012 12:00:00.000晚 ASN 9/3/2012 12:00:00.000晚 ASN 9/4/2012 10:06:00.000晚 ASN 9/5/2012 8:00:00.000晚 ASN 9/6/2012 3:00:00.000晚 ASN 9/6/2012 2:00:00.000晚 ASN 8/31/2012 12:00:00.000晚 ASN 8/31/2012 12:00:00.000晚 ASN 8/31/2012 12:00:00.000早期 ASN 8/15/2012 12:00:00.000我需要按SPGI01_INSTANCE_TYPE_C"列分组，并将每个星期天到最后六个星期的星期天分组.在这里我粘贴了两个示例表，一个表是我想要的，另一个表是我拥有的.给我解决方案.
我的查询是，
SELECT distinct I01.[SPGI01_INSTANCE_TYPE_C],计数(I01.[SPGI01_INSTANCE_TYPE_C])从 [SUPER-G].[dbo].[CSPGI01_ASN_ACCURACY] I01，[SUPER-G].[dbo].[CSPGI50_VALID_INSTANCE_TYPE] I50在哪里I01.[SPGA02_BUSINESS_TYPE_C] = '产品'和I01.[SPGA03_REGION_C] in('ap','na','sa','eu')和I01.[SPGI01_SUB_BUSINESS_TYPE_C] = 'PRD'和(I01.[SPGI01_CREATE_S] 在 '2012-01-01 12:00:00.000' 和 DATEADD(day , 7, '2012-01-15 00:00:00.000') 之间和I01.[SPGI01_EXCEPTIONED_F] = 'N'和I01.[SPGI01_DISPUTED_F] != 'Y'和I50.[SPGI50_INSTANCE_TYPE_C] = I01.[SPGI01_INSTANCE_TYPE_C]和I50.[SPGA04_RATING_ELEMENT_D] = 1按 I01 分组.[SPGI01_INSTANCE_TYPE_C]我对您发布的数据做了一些假设.
首先，您发布的值都将年份声明为 2011 但作为列标题的最终结束日期与 2011 不对应，它们是 Sunday 值，所以我更改了数据.2012 的 Early ASN 8/15/2011 12:00 的最后一个条目，我相信应该是一个 Late ASN 条目，否则总数匹配.p>
要获得结果，您需要应用 PIVOT 函数.此函数允许您聚合值，然后将它们转换为列.
SET DATEFIRST 1 -- 设置这个以便一周的开始是星期日选择实例类型，sum([39]) 作为 Sep_23，sum([38]) 作为 Sep_16，sum([37]) 作为 Sep_09，sum([36]) 作为 Sep_02，sum([35]) 作为 Aug_26，sum([34]) as Aug_19从(选择 SPGI01_INSTANCE_TYPE_C 作为 InstanceType，[39]、[38]、[37]、[36]、[35]、[34]从(选择 SPGI01_INSTANCE_TYPE_C，DatePart(wk, SPGI01_CREATE_S) WeekNo,DATEADD(DAY, 7 -DATEPART(WEEKDAY,SPGI01_CREATE_S),SPGI01_CREATE_S) 周末从表 1) X枢(计数(周末)在 ([39], [38], [37], [36], [35], [34])) p) x1按 InstanceType 和汇总分组参见 SQL Fiddle with Demo
Currently am working on the report. What i need is sample table,
Instance Type Sep-23 Sep-16 Sep-09 Sep-02 Aug-26 Aug-19
-------------------------------------------------------------------------
Early ASN 4 2 4 1 1 2
Late ASN 2 1 5 3 1 1
Sum 6 3 9 4 2 3
But the Actual Table is,
SPGI01_INSTANCE_TYPE_C SPGI01_CREATE_S
--------------------------------------------------------------
Early ASN 9/17/2012 12:00:00.000
Early ASN 9/18/2012 10:06:11.000
Early ASN 9/19/2012 8:00:04.000
Early ASN 9/20/2012 3:00:05.000
Early ASN 9/10/2012 12:00:07.000
Early ASN 9/11/2012 12:00:32.000
Early ASN 9/3/2012 12:00:17.000
Early ASN 9/4/2012 10:06:00.000
Early ASN 9/5/2012 8:00:00.000
Early ASN 9/6/2012 3:00:00.000
Early ASN 8/31/2012 12:00:00.000
Early ASN 8/26/2012 12:00:00.000
Early ASN 8/14/2012 12:00:00.000
Early ASN 8/15/2012 12:00:00.000
Late ASN 9/17/2012 12:00:00.000
Late ASN 9/18/2012 10:06:00.000
Late ASN 9/11/2012 12:00:00.000
Late ASN 9/3/2012 12:00:00.000
Late ASN 9/4/2012 10:06:00.000
Late ASN 9/5/2012 8:00:00.000
Late ASN 9/6/2012 3:00:00.000
Late ASN 9/6/2012 2:00:00.000
Late ASN 8/31/2012 12:00:00.000
Late ASN 8/31/2012 12:00:00.000
Late ASN 8/31/2012 12:00:00.000
Early ASN 8/15/2012 12:00:00.000
I need to group by the "SPGI01_INSTANCE_TYPE_C" column and group the each week sunday upto last six week sundays.Here i pasted two sample tables one table is what i want and another table is what i have. Give me the solution for this.
My query is,
SELECT distinct I01.[SPGI01_INSTANCE_TYPE_C],
count (I01.[SPGI01_INSTANCE_TYPE_C])
FROM [SUPER-G].[dbo].[CSPGI01_ASN_ACCURACY] I01,
[SUPER-G].[dbo].[CSPGI50_VALID_INSTANCE_TYPE] I50
where
I01.[SPGA02_BUSINESS_TYPE_C] = 'prod'
and
I01.[SPGA03_REGION_C] in( 'ap','na','sa','eu')
and
I01.[SPGI01_SUB_BUSINESS_TYPE_C] = 'PRD'
and
(I01.[SPGI01_CREATE_S] between '2012-01-01 12:00:00.000' AND DATEADD(day , 7, '2012-01-15 00:00:00.000'))
and
I01.[SPGI01_EXCEPTIONED_F] = 'N'
and
I01.[SPGI01_DISPUTED_F] != 'Y'
and
I50.[SPGI50_INSTANCE_TYPE_C] = I01.[SPGI01_INSTANCE_TYPE_C]
and
I50.[SPGA04_RATING_ELEMENT_D] = 1
group by I01.[SPGI01_INSTANCE_TYPE_C]
解决方案
I made a few assumptions about the data that you posted.
First, the values you posted all state the year as 2011 but the final end dates as column headers do not correspond to 2011, they are the Sunday values for 2012 so I altered the data. Also the final entry of Early ASN 8/15/2011 12:00, I believe is supposed to be a Late ASN entry otherwise the totals to do match up.
To get the results, you want you will want to apply the PIVOT function. This function allows you to aggregate the values and then convert them to columns.
SET DATEFIRST 1 -- set this so the start of the week is Sunday
select InstanceType,
sum([39]) as Sep_23,
sum([38]) as Sep_16,
sum([37]) as Sep_09,
sum([36]) as Sep_02,
sum([35]) as Aug_26,
sum([34]) as Aug_19
from
(
select SPGI01_INSTANCE_TYPE_C as InstanceType,
[39], [38], [37], [36], [35], [34]
from
(
select SPGI01_INSTANCE_TYPE_C,
DatePart(wk, SPGI01_CREATE_S) WeekNo,
DATEADD(DAY, 7 -DATEPART(WEEKDAY,SPGI01_CREATE_S),SPGI01_CREATE_S) WeekEnd
from table1
) x
pivot
(
count(WeekEnd)
for weekno in ([39], [38], [37], [36], [35], [34])
) p
) x1
group by InstanceType with rollup
See SQL Fiddle with Demo
相关文章