exp: SELECT V_ETMSRB.CU_NO AS 代号, CU00.CU_NAME AS 客户名称, SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)<0 THEN L_AMT-R_AMT ELSE 0 END) AS 期初余额, SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN L_AMT ELSE 0 END) AS [本期发生额/借方], SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN R_AMT ELSE 0 END) AS [本期发生额/贷方], SUM(L_AMT-R_AMT) AS 期末余额 FROM V_ETMSRB LEFT OUTER JOIN CU00 ON V_ETMSRB.CU_NO = CU00.CU_NO WHERE (V_ETMSRB.WA_NO LIKE '%WA_NO%%' AND V_ETMSRB.AR_DATE <= '%mEDate%') GROUP BY V_ETMSRB.CU_NO, CU00.CU_NAME HAVING SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)<0 THEN L_AMT-R_AMT ELSE 0 END)<>0 OR SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN L_AMT ELSE 0 END)<>0 OR SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN R_AMT ELSE 0 END)<>0 ORDER BY V_ETMSRB.CU_NO
SELECT V_ETMSRB.CU_NO AS 代号, CU00.CU_NAME AS 客户名称, SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)<0 THEN L_AMT-R_AMT ELSE 0 END) AS 期初余额, SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN L_AMT ELSE 0 END) AS [本期发生额/借方], SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN R_AMT ELSE 0 END) AS [本期发生额/贷方], SUM(L_AMT-R_AMT) AS 期末余额 FROM V_ETMSRB LEFT OUTER JOIN CU00 ON V_ETMSRB.CU_NO = CU00.CU_NO WHERE (V_ETMSRB.WA_NO LIKE '%WA_NO%%' AND V_ETMSRB.AR_DATE <= '%mEDate%') GROUP BY V_ETMSRB.CU_NO, CU00.CU_NAME HAVING SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)<0 THEN L_AMT-R_AMT ELSE 0 END)<>0 OR SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN L_AMT ELSE 0 END)<>0 OR SUM(CASE WHEN DateDiff(d, '%mSDate%', AR_DATE)>=0 THEN R_AMT ELSE 0 END)<>0 ORDER BY V_ETMSRB.CU_NO
销售表:
2007-1-1 200rmb
2007-1-3 400rmb
2007-1-5 600rmb
收款表
2007-1-10 300rmb
2007-1-12 200rmb
2007-1-15 500rmb
那么收款为1000元,销售也1200元,那么应该是1-5的销售还有200未收。所以,如果查询3-1的帐龄,那么该客户有60天内的未收款200元
这样设计数据库是不太科学的。
建议在销售表中这样设置:
日期 金额 未收款金额
然后通过增加核销功能来记录