跨越折扣分摊计算俩次月结之间的差额；

1.需求描述：由于本月可以修改历史成交单，在本月做月结的时候会将历史修改过成交单的月份重新做月结，现需求要求本次月结完后折扣与前一次月结折扣之间的差额；分摊到明细；

2.思路说明：

>1.首先找出做过跨越修改的成交单，及修改日期为当月成交日期不为当月的记录；

>2.去分摊明细表中做对比；

 1 SELECT B.DEALNO,
 2        B.DEALDATE,
 3        B.CREATETIME + 0,
 4        B.PRODUCTCODE,
 5        B.DEALCURRENCY,
 6        B.OUTCURRENCY,
 7        B.MEMBERID,
 8        B.BSFLAG,
 9        B.DISCOUNT,
10        B.DISCOUNTCNY,
11        B.DISCOUNTUSD,
12        B.DISCOUNTAMOUNT,
13        LAG(B.DISCOUNTAMOUNT, 1, 0) OVER(PARTITION BY B.DEALNO, B.BSFLAG /*,b.status*/ ORDER BY B.ID DESC) LAGAMOUNT,
14        B.DISCOUNTAMOUNTCNY,
15        B.DISCOUNTAMOUNTUSD,
16        B.DISCOUNTTAX,
17        B.DISCOUNTTAXCNY,
18        B.DISCOUNTTAXUSD,
19        B.STATUS,
20        B.RNUM
21        --,LAG(b.discount,1,b.discount) OVER (PARTITION BY b.dealno,b.bsflag/*,b.status*/ ORDER BY b.createtime DESC ) lag_discount
22       ,
23        B.DISCOUNTAMOUNT - LAG(B.DISCOUNTAMOUNT, 1, 0) OVER(PARTITION BY B.DEALNO, B.BSFLAG /*,b.status*/ ORDER BY B.ID DESC) LAG_DISCOUNTAMOUNT
24   FROM (SELECT B.ID,
25                B.DEALNO,
26                B.DEALDATE,
27                B.CREATETIME CREATETIME,
28                B.PRODUCTCODE,
29                B.DEALCURRENCY,
30                B.OUTCURRENCY,
31                B.MEMBERID,
32                B.BSFLAG,
33                B.DISCOUNT,
34                B.DISCOUNTCNY,
35                B.DISCOUNTUSD,
36                B.DISCOUNTAMOUNT,
37                B.DISCOUNTAMOUNTCNY,
38                B.DISCOUNTAMOUNTUSD,
39                B.DISCOUNTTAX,
40                B.DISCOUNTTAXCNY,
41                B.DISCOUNTTAXUSD,
42                B.STATUS,
43                ROW_NUMBER() OVER(PARTITION BY B.DEALNO, B.BSFLAG, B.STATUS ORDER BY B.ID DESC) RNUM
44         
45           FROM BASE_MEMBER_DEALER_DISCOUNT B
46          WHERE B.DEALNO IN (SELECT M.NO
47                               FROM HIS_PRODUCT_DEAL_MAIN M
48                              WHERE TO_CHAR(M.MODIFYTIME, 'yyyymm') = '201709'
49                                AND TO_CHAR(M.DEALDATE, 'yyyymm') <> '201709'
50                                AND M.STATE = 9)) B
51  WHERE RNUM < 3

View Code

第二版

 1 WITH A1 AS
 2 ---A1找出跨月修改的数据；37跳数据
 3  (SELECT M.NO A1NO
 4     FROM HIS_PRODUCT_DEAL_MAIN M
 5    WHERE TO_CHAR(M.MODIFYTIME, 'yyyymm') = '201709'
 6      AND TO_CHAR(M.DEALDATE, 'yyyymm') <> '201709'
 7      AND M.STATE = 9
 8   
 9   ),
10 A2 AS
11 --根据跨越修改的数据找到分摊数据 ；
12  (SELECT *
13     FROM (SELECT B.DEALNO A2NO,
14                  B.PRODUCTCODE A2CODE,
15                  B.BSFLAG B2BSFLAG,
16                  B.DISCOUNTAMOUNT A2AMOUNT,
17                  B.STATUS B2STATUS,
18                  B.ISWRITEOFF,
19                  ROW_NUMBER() OVER(PARTITION BY B.DEALNO, B.BSFLAG ORDER BY B.ID DESC) RN
20             FROM A1 A
21             LEFT JOIN BASE_MEMBER_DEALER_DISCOUNT B
22               ON A.A1NO = B.DEALNO
23            WHERE B.STATUS = 0 /* AND b.iswriteoff=0 AND EXISTS (SELECT 1 FROM A1 A WHERE  B.DEALNO  =  A.A1NO)  */
24           )
25    WHERE RN = 1)
26 
27 -- FROM A1 A , BASE_MEMBER_DEALER_DISCOUNT B 
28 --  WHERE A.A1NO=B.DEALNO  AND  B.STATUS = 0  /* AND b.iswriteoff=0 AND EXISTS (SELECT 1 FROM A1 A WHERE  B.DEALNO  =  A.A1NO)  */ ) WHERE rn=1  )
29 SELECT * FROM A2; --45跳数据 应该为37*2=74条 ；
30 
31 , A3 AS
32 --找到无效数据中最大id的那条数据 ；
33 (
34   SELECT S.DEALNO A3NO,
35          S.PRODUCTCODE A3CODE,
36          S.DISCOUNTAMOUNT A3AMOUNT,
37          S.BSFLAG B3BSFLAG,
38          ROW_NUMBER() OVER(PARTITION BY S.PRODUCTCODE, S.DEALNO, S.BSFLAG ORDER BY S.CREATETIME DESC) RN
39     FROM BASE_MEMBER_DEALER_DISCOUNT S
40    WHERE S.STATUS = 1), A4 AS (SELECT AA2.A2NO,
41                                 AA2.A2CODE,
42                                 AA2.B2BSFLAG,
43                                 AA2.A2AMOUNT,
44                                 AA3.A3AMOUNT,
45                                 AA2.A2AMOUNT - AA3.A3AMOUNT AS CHAE
46                            FROM A2 AA2
47                            LEFT JOIN A3 AA3
48                              ON AA2.A2NO = AA3.A3NO
49                             AND AA2.A2CODE = AA3.A3CODE
50                             AND AA2.B2BSFLAG = AA3.B3BSFLAG
51                           WHERE AA3.RN = 1)
52     SELECT * FROM A4;

View Code