怎么计算利息,

create table 发货表(发货日 datetime,客户 varchar(10),日发货金额 numeric(12,4))goinsert 发货表 select '2007-07-01','何健铭',   77745.00 insert 发货表 select '2007-08-05','金和兴',   74120.00 insert 发货表 select '2007-08-06','李克秋',   33834.00 insert 发货表 select '2007-09-01','伦祖坤',   24055.00 gocreate table 收款表(收款日 datetime,客户 varchar(10),日收款金额 numeric(12,4))goinsert 收款表 select '2007-07-01','何健铭',77745.00 insert 收款表 select '2007-08-15','金和兴',70000.00 insert 收款表 select '2007-08-26','李克秋',33834.00 insert 收款表 select '2007-11-05','伦祖坤',20000.00 ---1select 客户,发货总额,收款总额,应收欠款,利息=(case when datediff(d,发货日,收款日) between 3 and 30 then 应收欠款*0.0085              when datediff(d,发货日,收款日) BETWEEN 30 and 44  then 应收欠款*0.001 when datediff(d,发货日,收款日) BETWEEN 45 and 60  then 应收欠款*0.00125 when datediff(d,发货日,收款日) >61 then 应收欠款*0.0015           else 0           end)from (select a.客户,max(a.发货日) as 发货日,max(b.收款日) as 收款日,发货总额 = sum(日发货金额),              收款总额 = sum(日收款金额),sum(日发货金额 - 日收款金额) as 应收欠款from 发货表 aleft join 收款表 bon a.客户 = b.客户group by a.客户) a/*客户         发货总额                                     收款总额                                     应收欠款                                     利息                                       ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 何健铭        77745.0000                               77745.0000                               .0000                                    .000000金和兴        74120.0000                               70000.0000                               4120.0000                                35.020000李克秋        33834.0000                               33834.0000                               .0000                                    .000000伦祖坤        24055.0000                               20000.0000                               4055.0000                                6.082500（所影响的行数为 4 行）*/----2select a.客户,发货总额 = sum(日发货金额),              收款总额 = sum(日收款金额),              sum(日发货金额 - 日收款金额) as 应收欠款,              利息=sum((case when datediff(d,发货日,收款日) between 3 and 30 then 日发货金额*0.0085              when datediff(d,发货日,收款日) BETWEEN 30 and 44  then 日发货金额*0.001               when datediff(d,发货日,收款日) BETWEEN 45 and 60  then 日发货金额*0.00125               when datediff(d,发货日,收款日) >61 then 日发货金额*0.0015           else 0           end))from 发货表 aleft join 收款表 bon a.客户 = b.客户group by a.客户/*客户         发货总额                                     收款总额                                     应收欠款                                     利息                                       ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 何健铭        77745.0000                               77745.0000                               .0000                                    .000000000金和兴        74120.0000                               70000.0000                               4120.0000                                630.020000000李克秋        33834.0000                               33834.0000                               .0000                                    287.589000000伦祖坤        24055.0000                               20000.0000                               4055.0000                                36.082500000（所影响的行数为 4 行）*/drop table 发货表, 收款表
------解决方案--------------------
也不知道结果对不对,你试了再说吧.今天加班,随便写了一下.晚上再看.

SQL code
/*借老龟的数据*/create table tb_out(dt datetime,uname varchar(10),charge decimal(18,2))insert into tb_out select '2007-07-01','何健铭',77745.00               union all select '2007-08-05','金和兴',74120.00               union all select '2007-08-06','李克秋',33834.00               union all select '2007-09-01','伦祖坤',24055.00               union all select '2007-09-10','李奎'  ,18000.00               union all select '2007-09-10','何健铭',28000.00           union all select '2007-09-12','何健铭',60000.00create table tb_in(dt datetime,uname varchar(10),charge decimal(18,2))insert into tb_in select '2007-07-01','何健铭',77745.00union all select '2007-08-15','金和兴',70000.00   union all select '2007-08-16','金和兴',-10000.00             union all select '2007-08-26','李克秋',33834.00               union all select '2007-11-05','伦祖坤',20000.00               union all select '2007-11-25','伦祖坤',4055.00               union all select '2007-09-15','何健铭',30000.00       union all select '2007-09-20','何健铭',30000.00       union all select '2007-09-25','何健铭',15000.00 GO/*我设发款表中同一个人，同一天，只有一笔记录。因为，若同一人同一天有多笔记录，那么对于计算利息，多笔无意义,取的仍是同一人同一天的总量。收款表同理。 当然，如果同一人，同天，同表中有多条记录，请先将数据整理为同一人一天一笔记录的结果。--------------------------SELECT * INTO #outTRUNCATE TABLE tb_outINSERT tb_out SELECT dt,uname,sum(charge) FROM #out GROUP BY dt,unameDROP TABLE #out--------------------------方法就是这样，我在语句中不再予以处理。*//*本不需要临时表,为了写语句方便,故用了临时表.有不怕烦的,自己改成一条语句.估计要打两页了.^^*/SELECT IDENTITY(int) IDI/*因为同人同表同表只有一条记录(上面已说明),所以可以区别记录，不需要用identity列，我为了写语句方便故用了identity列，其它同理*/,    *,sci=(SELECT SUM(charge) FROM tb_in  WHERE uname=a.uname AND dt<=a.dt) INTO #1 FROM tb_in aSELECT IDENTITY(int) IDO,    *,sco=(SELECT SUM(charge) FROM tb_out WHERE uname=a.uname AND dt<=a.dt) INTO #2 FROM tb_out aSELECT IDENTITY(INT) NID , a.uname, a.dt dti,a.charge ci,b.dt dto,b.charge co,sci,sco INTO #x    FROM #1 aLEFT JOIN #2 b    ON        (sco>=sci AND a.uname=b.uname AND 1>(SELECT COUNT(1) FROM #2 WHERE uname=b.uname AND dt<b.dt AND sco>=a.sci))        OR        (sci>=sco AND a.uname=b.uname AND (SELECT sci FROM #1 WHERE uname=a.uname AND IDI=(SELECT MAX(IDI) FROM #1 WHERE IDI<a.IDI AND uname=a.uname))<sco)        AND a.uname=b.uname    ORDER BY a.uname,a.dtSELECT *,CASE WHEN preLeave=0 AND co<ci THEN co        WHEN preLeave>0 THEN preLeave        ELSE ci        END 当次被拆额,    (CASE WHEN preLeave=0 AND co<ci THEN co        WHEN preLeave>0 THEN preLeave        ELSE ci        END    ) * y.perc/100 利息    FROM    (SELECT *,ISNULL((SELECT sci-sco FROM #x WHERE uname=a.uname AND NID=(SELECT MAX(NID) FROM #x WHERE NID<a.NID AND uname=a.uname)),0) preLeave FROM #x a) xINNER JOIN    (    SELECT minSPAN=0,maxSPAN=2,perc=0    UNION ALL    SELECT minSPAN=3,maxSPAN=29,perc=0.85    UNION    SELECT minSPAN=30,maxSPAN=44,perc=1    UNION    SELECT minSPAN=45,maxSPAN=60,perc=1.25    UNION    SELECT minSPAN=61,maxSPAN=100000,perc=1.5    ) y    ON DATEDIFF(dd,dto,dti) BETWEEN minSPAN AND maxSPAN/*1    何健铭    2007-07-01 00:00:00.000    77745.00    2007-07-01 00:00:00.000    77745.00    77745.00    77745.00    .00    0    2    .00    77745.00    .0000007    何健铭    2007-09-15 00:00:00.000    30000.00    2007-09-10 00:00:00.000    28000.00    107745.00    105745.00    .00    3    29    .85    28000.00    238.0000008    何健铭    2007-09-15 00:00:00.000    30000.00    2007-09-12 00:00:00.000    60000.00    107745.00    165745.00    2000.00    3    29    .85    2000.00    17.0000009    何健铭    2007-09-20 00:00:00.000    30000.00    2007-09-12 00:00:00.000    60000.00    137745.00    165745.00    -58000.00    3    29    .85    30000.00    255.00000010    何健铭    2007-09-25 00:00:00.000    15000.00    2007-09-12 00:00:00.000    60000.00    152745.00    165745.00    -28000.00    3    29    .85    15000.00    127.5000002    金和兴    2007-08-15 00:00:00.000    70000.00    2007-08-05 00:00:00.000    74120.00    70000.00    74120.00    .00    3    29    .85    70000.00    595.0000003    金和兴    2007-08-16 00:00:00.000    -10000.00    2007-08-05 00:00:00.000    74120.00    60000.00    74120.00    -4120.00    3    29    .85    -10000.00    -85.0000004    李克秋    2007-08-26 00:00:00.000    33834.00    2007-08-06 00:00:00.000    33834.00    33834.00    33834.00    .00    3    29    .85    33834.00    287.5890005    伦祖坤    2007-11-05 00:00:00.000    20000.00    2007-09-01 00:00:00.000    24055.00    20000.00    24055.00    .00    61    100000    1.50    20000.00    300.0000006    伦祖坤    2007-11-25 00:00:00.000    4055.00    2007-09-01 00:00:00.000    24055.00    24055.00    24055.00    -4055.00    61    100000    1.50    4055.00    60.825000*/GODROP TABLE tb_out,tb_in,#x,#1,#2GO