求助大神 关于MSSQL 回款 与 欠单的问题

客户编码  业务员 欠款金额        欠款日期        回款金额 剩余回款金额  剩余欠款金额
ah00003      李     0                           2014-12-31          300000
ah00003      李     239895.15          2015-01-26          0
ah00003      李     116884.8            2015-01-27          0
ah00012      张     12536                   2014-12-31        1000000
ah00012      张     20135 .36              2015-01-15          0
ah00012      张    1253677                2015-02-03            0
ah00012      张     569                        2015-02-04            0回款金额 是 一段时间内的回款的总金额
现在按欠款日期 把回款先抵掉日期比较前的欠款
想得出以下表格客户编码  业务员 欠款金额        欠款日期        剩余回款金额  剩余欠款金额
ah00003      李     0                           2014-12-31          300000                           0
ah00003      李     239895.15          2015-01-26           60104.85                    0
ah00003      李     116884.8            2015-01-27          0                                   56779.95
ah00012      张     12536                   2014-12-31               987464                    0
ah00012      张     20135 .36              2015-01-15          967628.64                    0
ah00012      张    1253677                2015-02-03             0                                  286348.36
ah00012      张     569                        2015-02-04              0                              569求大神！！

解决方案 »

  1.   

    ;WITH CTE AS(
    SELECT T1.客户编码,T1.业务员,T1.欠款金额,T1.欠款日期,T1.回款金额
    ,SUM(T2.欠款金额)总欠款金额
    FROM TB T1
    JOIN TB T2 ON T1.客户编码=T2.客户编码 AND T1.欠款日期>=T2.欠款日期
    GROUP BY T1.客户编码,T1.业务员,T1.欠款金额,T1.欠款日期,T1.回款金额
    )
    SELECT 客户编码,业务员,欠款金额,欠款日期
    ,CASE WHEN 回款金额>总欠款金额 THEN 回款金额-总欠款金额 ELSE 0 END 剩余回款金额
    ,CASE WHEN 回款金额<总欠款金额 AND 回款金额>总欠款金额-欠款金额 THEN 总欠款金额-回款金额
    WHEN 回款金额>总欠款金额 THEN 0
    ELSE 欠款金额
    END 剩余欠款金额
    FROM CTE
    ORDER BY 客户编码,欠款日期
      

  2.   

    你这个可以用游标，也可以不用，取决于客户编码的数量的多少，如果有很多，集合方式还是比游标快的另外，我把语句修改正了;WITH CTE AS(
    SELECT T1.客户编码,T1.业务员,T1.欠款金额,T1.欠款日期,T1.回款金额,SUM(T2.回款金额)总回款金额
    ,SUM(T2.欠款金额)总欠款金额
    FROM TB T1
    JOIN TB T2 ON T1.客户编码=T2.客户编码 AND T1.欠款日期>=T2.欠款日期
    GROUP BY T1.客户编码,T1.业务员,T1.欠款金额,T1.欠款日期,T1.回款金额
    )
    SELECT 客户编码,业务员,欠款金额,欠款日期--,回款金额,总欠款金额
    ,CASE WHEN 总回款金额>总欠款金额 THEN 总回款金额-总欠款金额 ELSE 0 END 剩余回款金额
    ,CASE WHEN 总回款金额<总欠款金额 AND 总回款金额>总欠款金额-欠款金额 THEN 总欠款金额-总回款金额
    WHEN 总回款金额>总欠款金额 THEN 0
    ELSE 欠款金额
    END 剩余欠款金额
    FROM CTE
    ORDER BY 客户编码,欠款日期
      

  3.   

    WITH /* 测试数据
    table1(客户编码,业务员,欠款金额,欠款日期,回款金额) AS (
        SELECT 'ah00003',N'李',Convert(money,0),'2014-12-31',Convert(money,300000) UNION ALL
        SELECT 'ah00003',N'李',239895.15,'2015-01-26',0 UNION ALL
        SELECT 'ah00003',N'李',116884.8,'2015-01-27',0 UNION ALL
        SELECT 'ah00012',N'张',12536,'2014-12-31',1000000 UNION ALL
        SELECT 'ah00012',N'张',20135.36,'2015-01-15',0 UNION ALL
        SELECT 'ah00012',N'张',1253677,'2015-02-03',0 UNION ALL
        SELECT 'ah00012',N'张',569,'2015-02-04',0 
    ), */
    t1 AS (
        SELECT *,
               ROW_NUMBER() OVER(PARTITION BY 客户编码 ORDER BY 欠款日期) rn
          FROM table1
    ),
    t2 AS (
        SELECT *,
               Convert(money,回款金额-欠款金额) 剩余金额
          FROM t1
         WHERE rn = 1
        UNION ALL
        SELECT t1.*,
               Convert(money,t2.剩余金额+t1.回款金额-t1.欠款金额)
          FROM t2
          JOIN t1
            ON t2.客户编码 = t1.客户编码
           AND t2.rn+1 = t1.rn
    )
        SELECT 客户编码, 业务员, 欠款金额, 欠款日期,
               CASE WHEN 剩余金额 > 0
                    THEN 剩余金额
                    ELSE 0
               END 剩余回款金额,
               CASE WHEN 剩余金额 < 0
                    THEN ABS(剩余金额)
                    ELSE 0
               END 剩余欠款金额
         FROM T2
     ORDER BY 客户编码, rn
    客户编码 业务员              欠款金额 欠款日期            剩余回款金额          剩余欠款金额
    -------- ------ --------------------- ---------- --------------------- ---------------------
    ah00003  李                    0.0000 2014-12-31             300000.00                  0.00
    ah00003  李               239895.1500 2015-01-26              60104.85                  0.00
    ah00003  李               116884.8000 2015-01-27                  0.00              56779.95
    ah00012  张                12536.0000 2014-12-31             987464.00                  0.00
    ah00012  张                20135.3600 2015-01-15             967328.64                  0.00
    ah00012  张              1253677.0000 2015-02-03                  0.00             286348.36
    ah00012  张                  569.0000 2015-02-04                  0.00             286917.36
      

  4.   


    xiexie
      

  5.   

    use tempdbgo
    create table t1
    (
    客户编码 nvarchar(100),
    业务员 nvarchar(100),
    欠款金额 money,
    欠款日期 datetime,
    回款金额 money
    )
    insert into t1(客户编码,业务员,欠款金额,欠款日期,回款金额)
    SELECT 'ah00003',N'李',Convert(money,0),'2014-12-31',Convert(money,300000) UNION ALL
    SELECT 'ah00003',N'李',239895.15,'2015-01-26',0 UNION ALL
    SELECT 'ah00003',N'李',116884.8,'2015-01-27',0 UNION ALL
    SELECT 'ah00012',N'张',12536,'2014-12-31',1000000 UNION ALL
    SELECT 'ah00012',N'张',20135.36,'2015-01-15',0 UNION ALL
    SELECT 'ah00012',N'张',1253677,'2015-02-03',0 UNION ALL
    SELECT 'ah00012',N'张',569,'2015-02-04',0 
    ;with t2 as
    (
    select *,序号=ROW_NUMBER() over (PARTITION by 客户编码 order by 欠款日期) from t1 ),
    t4 as
    (
    select * ,剩余金额 =(select SUM(isnull(a.回款金额,0))-SUM(ISNULL(a.欠款金额,0)) from t2 a where a.序号<=b.序号 and a.客户编码=b.客户编码)from t2 b 

    )
      SELECT 客户编码, 业务员, 欠款金额, 欠款日期,
               CASE WHEN 剩余金额 > 0
                    THEN 剩余金额
                    ELSE 0
               END 剩余回款金额,
               CASE WHEN 剩余金额 < 0
                    THEN ABS(剩余金额)
                    ELSE 0
               END 剩余欠款金额
         FROM T4
     ORDER BY 客户编码, 序号