按季度分组数据

id    transAmt      transDate             payToOffer  backToOrder
1      20.32002007-02-03 00:00:00.00000
2      23.32002007-03-03 00:00:00.00010
3     100.32002007-03-03 00:00:00.00000
4     200.32002007-05-03 00:00:00.00000
5     220.32002007-04-03 00:00:00.00010
6     10.32002007-05-03 00:00:00.00000
7     20.32002007-05-03 00:00:00.00000
8     20.32002007-06-03 00:00:00.00001
9     20.32002007-07-03 00:00:00.00000
10     20.32002007-07-03 00:00:00.00000
11     20.32002007-08-03 00:00:00.00001
12     20.32002007-09-03 00:00:00.00000
13     20.32002007-10-03 00:00:00.00000
14     20.32002007-10-03 00:00:00.00001
15     20.32002007-12-03 00:00:00.00000
16     20.32002007-12-03 00:00:00.00000
17     20.32002007-12-03 00:00:00.00001
18     20.32002007-12-03 00:00:00.00010

 

我想根据每季度来对transAmt求和,再根据

clearAmt  根据 payToOffer=1       or backToOrder=1

unclearAmt根据 payToOffer=0 and backToOrder=0

最后达到如下效果

puarter  clearAmt  unclearAmt  
1         23.3200   123.64
2         220.3200  251.28
3         20.32     60.96
4         60.96     40.64

 

 

 

declare @t table (id int,    transAmt   money,   transDate  datetime,            payToOffer  int,backToOrder int)
insert into @t  select  1,     20.3200, '2007-02-03 00:00:00.000', 0, 0 
union all          select 2,      23.3200, '2007-03-03 00:00:00.000', 1, 0 
union all          select 3,     100.3200, '2007-03-03 00:00:00.000', 0, 0 
union all          select 4,     200.3200, '2007-05-03 00:00:00.000', 0, 0 
union all          select 5,     220.3200, '2007-04-03 00:00:00.000', 1, 0 
union all          select 6,     10.3200, '2007-05-03 00:00:00.000', 0, 0 
union all          select 7,     20.3200, '2007-05-03 00:00:00.000', 0, 0 
union all          select 8,     20.3200, '2007-06-03 00:00:00.000', 0, 1 
union all          select 9,     20.3200, '2007-07-03 00:00:00.000', 0, 0 
union all          select 10,     20.3200, '2007-07-03 00:00:00.000', 0, 0 
union all          select 11,     20.3200, '2007-08-03 00:00:00.000', 0, 1 
union all          select 12,     20.3200, '2007-09-03 00:00:00.000', 0, 0 
union all          select 13,     20.3200, '2007-10-03 00:00:00.000', 0, 0 
union all          select 14,     20.3200, '2007-10-03 00:00:00.000', 0, 1 
union all          select 15,     20.3200, '2007-12-03 00:00:00.000', 0, 0 
union all          select 16,     20.3200, '2007-12-03 00:00:00.000', 0, 0 
union all          select 17,     20.3200, '2007-12-03 00:00:00.000', 0, 1 
union all          select 18,     20.3200, '2007-12-03 00:00:00.000', 1, 0

select
a=datepart(qq,transDate),
b=sum(case when paytooffer=1 or backtoorder =1 then transamt end),
c=sum(case when paytooffer=0 and backtoorder =0 then transamt end)
from @t group by datepart(qq,transDate)

/*
a                          b                 c
----------------------------------------------
1             23.3200                120.6400
2           240.6400               230.9600
3              20.3200              60.9600
4               60.9600             60.9600
*/