¿Cómo agrupar por cada semana hasta las últimas seis semanas los domingos en sql?
Actualmente estoy trabajando en el informe. Lo que necesito es una tabla de muestra,
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
Pero la tabla real es,
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
Necesito agrupar por la columna "SPGI01_INSTANCE_TYPE_C" y agrupar cada semana de domingo a domingo de seis semanas. Aquí pegué dos tablas de muestra, una tabla es lo que quiero y otra tabla es lo que tengo. Dame la solución para esto.
Mi consulta es,
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]