一个关于账龄计算的问题，下面是源数据表
客户 单据类型 余额 单据日期 账龄
A 应收 1292301 2010-1-31 00:00 121
A 应收 1139292 2010-2-28 00:00 93
A 应收 1152316.8 2010-3-23 00:00 70
A 应收 78148.8 2010-3-23 00:00 70
A 应收 1230465.6 2010-4-28 00:00 34
A 应收 1204416 2010-5-25 00:00 7
A 收款 -1204416 2010-2-28 00:00 93
A 收款 -1139292 2010-3-31 00:00 62
A 收款 -1230465.6 2010-4-30 00:00 32
A 收款 -1230465.6 2010-5-31 00:00 1
B 应收 196668 2010-1-31 00:00 121
B 应收 8866 2010-2-25 00:00 96
B 应收 154677.6 2010-2-28 00:00 93
B 应收 508741.2 2010-3-23 00:00 70
B 应收 453157.2 2010-4-28 00:00 34
B 应收 411469.2 2010-5-25 00:00 7
B 收款 -196668 2010-3-31 00:00 62
B 收款 -154677.6 2010-4-30 00:00 32
B 收款 -8866 2010-4-30 00:00 32
B 收款 -508741.2 2010-5-31 00:00 1
C 应收 506268 2010-1-21 00:00 131
C 应收 536491.2 2010-2-25 00:00 96
C 应收 633080 2010-3-23 00:00 70
C 应收 426927.2 2010-4-19 00:00 43
C 应收 913399.2 2010-5-17 00:00 15
C 应收 959968.8 2009-12-30 00:00 153
C 应收 584511.2 2009-9-30 00:00 244
C 应收 974943.2 2009-4-30 00:00 397
C 应收 45332.82 2009-1-23 00:00 494
需要得到以下结果
客户 应收余额 账期内本币金额 1-30天余额 30--60天余额 61-90天余额 91-180天余额 181-270天余额 271-360天余额 361-720天余额 721天以上余额
A 1292301 0 1204416 87885 0 0 0 0 0 0
B 864626.4 0 411469.2 453157.2 0 0 0 0 0 0
C 5580921.62 0 913399.2 426927.2 633080 2002728 584511.2 0 1020276.02 0
SQL 语句怎么写啊，请求高手解答
------解决方案--------------------
- SQL code
--测试环境if object_id('[tb]') is not null drop table [tb]go create table [tb]([客户] varchar(1),[单据类型] varchar(4),[余额] numeric(10,1),[单据日期] datetime,账龄 int)insert [tb]select 'A','应收','1292301','2010-1-31 00:00','121' union allselect 'A','应收','1139292','2010-2-28 00:00','93' union allselect 'A','应收','1152316.8','2010-3-23 00:00','70' union allselect 'A','应收','78148.8','2010-3-23 00:00','70' union allselect 'A','应收','1230465.6','2010-4-28 00:00','34' union allselect 'A','应收','1204416','2010-5-25 00:00','7' union allselect 'A','收款','-1204416','2010-2-28 00:00','93' union allselect 'A','收款','-1139292','2010-3-31 00:00','62' union allselect 'A','收款','-1230465.6','2010-4-30 00:00','32' union allselect 'A','收款','-1230465.6','2010-5-31 00:00','1' union allselect 'B','应收','196668','2010-1-31 00:00','121' union allselect 'B','应收','8866','2010-2-25 00:00','96' union allselect 'B','应收','154677.6','2010-2-28 00:00','93' union allselect 'B','应收','508741.2','2010-3-23 00:00','70' union allselect 'B','应收','453157.2','2010-4-28 00:00','34' union allselect 'B','应收','411469.2','2010-5-25 00:00','7' union allselect 'B','收款','-196668','2010-3-31 00:00','62' union allselect 'B','收款','-154677.6','2010-4-30 00:00','32' union allselect 'B','收款','-8866','2010-4-30 00:00','32' union allselect 'B','收款','-508741.2','2010-5-31 00:00','1' union allselect 'C','应收','506268','2010-1-21 00:00','131' union allselect 'C','应收','536491.2','2010-2-25 00:00','96' union allselect 'C','应收','633080','2010-3-23 00:00','70' union allselect 'C','应收','426927.2','2010-4-19 00:00','43' union allselect 'C','应收','913399.2','2010-5-17 00:00','15' union allselect 'C','应收','959968.8','2009-12-30 00:00','153' union allselect 'C','应收','584511.2','2009-9-30 00:00','244' union allselect 'C','应收','974943.2','2009-4-30 00:00','397' union allselect 'C','应收','45332.82','2009-1-23 00:00','494' goselect 客户,sum(余额)as 应收余额,0 as 账期内本币金额,case when sum(case when 单据类型='应收'and 账龄 > 30 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 1 and 31 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 31 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 1 and 30 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 30 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 1 and 30 then 余额 else 0 end) endend as [1-30天余额],case when sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 60 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 31 and 60 then 余额 else 0 end) endend as[31-60天余额],case when sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 90 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 61 and 90 then 余额 else 0 end) endend as[61-90天余额],case when sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 180 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 91 and 180 then 余额 else 0 end) endend as[91-180天余额],case when sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 270 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 181 and 270 then 余额 else 0 end) endend as[181-270天余额],case when sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 271 and 360 then 余额 else 0 end) endend as[271-360天余额],case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end) else case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end)<0 then 0 else sum(case when 单据类型='应收'and 账龄 > 360 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) + sum(case when 单据类型='应收'and 账龄 between 361 and 720 then 余额 else 0 end) endend as[361-720天余额],case when sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end)>=0 then sum(case when 单据类型='应收'and 账龄 > 720 then 余额 else 0 end)+ sum(case when 单据类型='收款' then 余额 else 0 end) else 0end as[721天以上余额]from tbgroup by 客户--结果/*客户 应收余额 账期内本币金额 1-30天余额 31-60天余额 61-90天余额 91-180天余额 181-270天余额 271-360天余额 361-720天余额 721天以上余额---- ------------ ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------A 1292301.0 0 1204416.0 87885.0 0.0 0.0 0.0 0.0 0.0 0.0B 864626.4 0 411469.2 453157.2 0.0 0.0 0.0 0.0 0.0 0.0C 5580921.6 0 913399.2 426927.2 633080.0 2002728.0 584511.2 0.0 1020276.0 0.0(3 行受影响)*/