SELECT SUM(typea),GROUPF FROM ( select *, GROUPF = CASE WHEN (14-DATEPART(DAY,Tdate))< 0 THEN 1 ELSE 0 END from (select CONVERT(DATEtIME,'2008-12-1',101) as Tdate, 100 as typea UNION select CONVERT(DATEtIME,'2008-12-15',101) as Tdate, 100 as typea UNION select CONVERT(DATEtIME,'2008-12-12',101) as Tdate, 200 as typea UNION select CONVERT(DATEtIME,'2008-12-13',101) as Tdate, 100 as typea ) AS A ) as AA GROUP BY GROUPF
SELECT SUM(typea),GROUPF FROM ( select *, GROUPF = CASE WHEN (14-DATEPART(DAY,Tdate))< 0 THEN 1 ELSE 0 END from (select CONVERT(DATEtIME,'2008-12-1',101) as Tdate, 100 as typea UNION select CONVERT(DATEtIME,'2008-12-15',101) as Tdate, 100 as typea UNION select CONVERT(DATEtIME,'2008-12-12',101) as Tdate, 200 as typea UNION select CONVERT(DATEtIME,'2008-12-13',101) as Tdate, 100 as typea ) AS A ) as AA GROUP BY GROUPF
;with c as (select cast('2008-12-01' as datetime) Date union all select Date+1 from c where Date+1<'2009-01-01') select group1,[Date] from (select (dense_rank()over(order by datepart(wk,date))-1)/2+1 as group1,Date from C)T
FROM
(
select *, GROUPF = CASE WHEN (14-DATEPART(DAY,Tdate))< 0 THEN 1
ELSE 0 END
from
(select CONVERT(DATEtIME,'2008-12-1',101) as Tdate, 100 as typea
UNION
select CONVERT(DATEtIME,'2008-12-15',101) as Tdate, 100 as typea
UNION
select CONVERT(DATEtIME,'2008-12-12',101) as Tdate, 200 as typea
UNION
select CONVERT(DATEtIME,'2008-12-13',101) as Tdate, 100 as typea
) AS A
) as AA
GROUP BY GROUPF
FROM
(
select *, GROUPF = CASE WHEN (14-DATEPART(DAY,Tdate))< 0 THEN 1
ELSE 0 END
from
(select CONVERT(DATEtIME,'2008-12-1',101) as Tdate, 100 as typea
UNION
select CONVERT(DATEtIME,'2008-12-15',101) as Tdate, 100 as typea
UNION
select CONVERT(DATEtIME,'2008-12-12',101) as Tdate, 200 as typea
UNION
select CONVERT(DATEtIME,'2008-12-13',101) as Tdate, 100 as typea
) AS A
) as AA
GROUP BY GROUPF
as
(select cast('2008-12-01' as datetime) Date
union all
select Date+1 from c where Date+1<'2009-01-01')
select
group1,[Date]
from
(select (dense_rank()over(order by datepart(wk,date))-1)/2+1 as group1,Date from C)T