如何在sql中按最近六周日的每个星期分组? [英] How to group by the each week upto last six week sundays dates in sql?
问题描述
当前正在处理报告.我需要的是 样本表，
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
但是实际表是
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
我需要对"SPGI01_INSTANCE_TYPE_C"列进行分组，并将每周的星期日分组，直到最近六周的星期日分组.在这里，我粘贴了两个示例表，一个是我想要的表，另一个是我拥有的表.给我解决办法.
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.
我的查询是
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.
首先，您发布的所有值都将年份表示为2011，但作为列标题的最终结束日期与2011不对应，它们是2012的Sunday值，因此我更改了数据.也是Early ASN 8/15/2011 12:00的最后一个条目，我认为应该是Late ASN条目，否则总和要匹配.
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.
要获得结果，您希望应用 PIVOT 函数.此功能使您可以汇总值，然后将其转换为列.
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
请参见带有演示的SQL小提琴
这篇关于如何在sql中按最近六周日的每个星期分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
