金蝶Cloud 应收账龄分析表-按到期日查询SQL
select t.组织,t.客户,sum(t.余额*isnull(m.FEXCHANGERATE,1)) 期末余额,sum(isnull(t.[账期内],0)*isnull(m.FEXCHANGERATE,1)) [账期内],sum(isnull(t.[超账期1个月],0)*isnull(m.FEXCHANGERATE,1)) [超账期1个月],sum(isnull(t.[超账期2个月],0)*isnull(m.FEXCHANGERATE,1)) [超账期2个月],sum(isnull(t.[超账期3个月],0)*isnull(m.FEXCHANGERATE,1)) [超账期3个月],sum(isnull(t.[超账期4个月及以上],0)*isnull(m.FEXCHANGERATE,1)) [超账期4个月及以上]from(select t5.FNAME 组织,t4.FNAME 客户,sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) 余额,case when datediff(dd,t1.FENDDATE,getdate())<=0 then sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) end [账期内],case when datediff(dd,t1.FENDDATE,getdate())>0 and datediff(dd,t1.FENDDATE,getdate())<=30 then sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期1个月],case when datediff(dd,t1.FENDDATE,getdate())>30 and datediff(dd,t1.FENDDATE,getdate())<=60 then sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期2个月],case when datediff(dd,t1.FENDDATE,getdate())>60 and datediff(dd,t1.FENDDATE,getdate())<=90 then sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期3个月],case when datediff(dd,t1.FENDDATE,getdate())>90 then sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期4个月及以上],t1.FCURRENCYID 币别from t_AR_receivable t1--left join T_AR_RECEIVABLEENTRY t2 on t1.FID=t2.FIDleft join T_BD_CUSTOMER_L t4 on t4.FCUSTID=t1.FCUSTOMERID and t4.FLOCALEID=2052left join T_ORG_ORGANIZATIONS_L t5 on t1.FSALEORGID=t5.FORGID and t5.FLOCALEID=2052left join T_BD_CUSTOMER t10 on t4.FCUSTID=t10.FCUSTIDleft join T_BD_CUSTOMERGROUP_L t11 on t10.FPRIMARYGROUP=t11.FID and t11.FLOCALEID=2052left join t_AR_receivableFIN t17 on t1.FID=t17.FIDleft join T_AR_RECEIVABLEPLAN t18 ON t1.FID = t18.FID left join (SELECT data1.FSourceFromid,data1.FSrcBillId,data1.FSrcRowId,ISNULL(sum(data1.FCURWRITTENOFFAMOUNTFOR),0) fcurwrittenoffamountfor,ISNULL(sum(data1.FCURWRITTENOFFAMOUNT),0) fcurwrittenoffamount FROM (SELECT LogEntry.FSourceFromid,FSrcBillId,FSrcRowId,LogEntry.FCURWRITTENOFFAMOUNTFOR,LogEntry.FCURWRITTENOFFAMOUNT FROM T_AR_RECMacthLog PayLog INNER JOIN T_AR_RECMacthLogENTRY LogEntry ON PayLog.Fid = LogEntry.Fid WHERE LogEntry.FSourceFromid = 'AR_receivable') data1GROUP BY data1.FSourceFromid,data1.FSrcBillId,data1.FSrcRowId) armatchrecord ON t1.FId = ARmatchRecord.FSrcBillId AND t18.FEntryId = ARmatchRecord.FSrcRowIdleft join (SELECT data2.FSourceFromid,data2.FSrcBillId,data2.FSrcRowId,ISNULL(sum(data2.FCURWRITTENOFFAMOUNTFOR),0) fcurwrittenoffamountfor,ISNULL(sum(data2.FCURWRITTENOFFAMOUNT),0) fcurwrittenoffamount FROM (SELECT LogEntry.FSourceFromid,FSrcBillId,FSrcRowId,LogEntry.FCURWRITTENOFFAMOUNTFOR,LogEntry.FCURWRITTENOFFAMOUNT FROM T_AP_PAYMatchLog PayLog INNER JOIN T_AP_PAYMatchLogEntry LogEntry ON PayLog.Fid = LogEntry.Fid WHERE LogEntry.FSourceFromid = 'AR_receivable') data2 GROUP BY data2.FSourceFromid,data2.FSrcBillId,data2.FSrcRowId) apmatchrecord ON t1.FId = APmatchRecord.FSrcBillId AND t18.FEntryId = APmatchRecord.FSrcRowId where t1.FDOCUMENTSTATUS='C' and t11.FNAME<>'关联公司'group by t5.FNAME,t4.FNAME,t1.FENDDATE,t1.FCURRENCYIDhaving sum(t18.FPayAmountFor-ISNULL(APmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)-ISNULL(ARmatchRecord.FCURWRITTENOFFAMOUNTFOR,0)) <>0union allselect t5.FNAME 组织,t4.FNAME 客户,-SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) 收款金额,case when datediff(dd,t1.FDATE,getdate())<=0 then -SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) end [账期内],case when datediff(dd,t1.FDATE,getdate())>0 and datediff(dd,t1.FDATE,getdate())<=30 then -SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) end [超账期1个月],case when datediff(dd,t1.FDATE,getdate())>30 and datediff(dd,t1.FDATE,getdate())<=60 then -SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) end [超账期2个月],case when datediff(dd,t1.FDATE,getdate())>60 and datediff(dd,t1.FDATE,getdate())<=90 then -SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) end [超账期3个月],case when datediff(dd,t1.FDATE,getdate())>90 then -SUM(ISNULL(t2.FRECTOTALAMOUNTFOR, 0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR, 0)) end [超账期4个月及以上],t1.FCURRENCYID 币别from T_AR_RECEIVEBILL t1left join T_AR_RECEIVEBILLENTRY t2 on t1.FID=t2.FIDleft join T_BD_CUSTOMER_L t4 on t4.FCUSTID=t1.FCONTACTUNIT and t4.FLOCALEID=2052left join T_ORG_ORGANIZATIONS_L t5 on t1.FSALEORGID=t5.FORGID and t5.FLOCALEID=2052left join T_BD_CUSTOMER t10 on t4.FCUSTID=t10.FCUSTIDleft join T_BD_CUSTOMERGROUP_L t11 on t10.FPRIMARYGROUP=t11.FID and t11.FLOCALEID=2052left join (SELECT data.FSourceFromid,data.FSrcBillId,data.FSRCROWID,ISNULL(sum(data.FCURWRITTENOFFAMOUNTFOR),0) fcurwrittenoffamountfor,ISNULL(sum(data.FCURWRITTENOFFAMOUNT),0) fcurwrittenoffamount FROM (SELECT LogEntry.FSourceFromid,FSrcBillId,LogEntry.FSRCROWID,LogEntry.FCURWRITTENOFFAMOUNTFOR,LogEntry.FCURWRITTENOFFAMOUNT FROM T_AR_RECMacthLog PayLog INNER JOIN T_AR_RECMacthLogENTRY LogEntry ON PayLog.Fid = LogEntry.Fid WHERE LogEntry.FSourceFromid = 'AR_RECEIVEBILL') data GROUP BY data.FSourceFromid,data.FSrcBillId,data.FSRCROWID) t17 ON t1.FId = t17.FSrcBillId AND t2.FENTRYID = t17.FSRCROWIDwhere t1.FDOCUMENTSTATUS='C' and t11.FNAME<>'关联公司'group by t5.FNAME,t4.FNAME,t1.FDATE,t1.FCURRENCYIDunion allselect t5.FNAME 组织,t4.FNAME 客户,SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) 退款金额,case when datediff(dd,t1.FDATE,getdate())<=0 then SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [账期内],case when datediff(dd,t1.FDATE,getdate())>0 and datediff(dd,t1.FDATE,getdate())<=30 then SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期1个月],case when datediff(dd,t1.FDATE,getdate())>30 and datediff(dd,t1.FDATE,getdate())<=60 then SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期2个月],case when datediff(dd,t1.FDATE,getdate())>60 and datediff(dd,t1.FDATE,getdate())<=90 then SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期3个月],case when datediff(dd,t1.FDATE,getdate())>90 then SUM(ISNULL(t2.FREFUNDAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期4个月及以上],t1.FCURRENCYID 币别from T_AR_REFUNDBILL t1left join T_AR_REFUNDBILLENTRY t2 on t1.FID=t2.FIDleft join T_BD_CUSTOMER_L t4 on t4.FCUSTID=t1.FCONTACTUNIT and t4.FLOCALEID=2052left join T_ORG_ORGANIZATIONS_L t5 on t1.FSALEORGID=t5.FORGID and t5.FLOCALEID=2052left join T_BD_CUSTOMER t10 on t4.FCUSTID=t10.FCUSTIDleft join T_BD_CUSTOMERGROUP_L t11 on t10.FPRIMARYGROUP=t11.FID and t11.FLOCALEID=2052left join (SELECT data.FSourceFromid,data.FSrcBillId,data.FSRCROWID,ISNULL(sum(data.FCURWRITTENOFFAMOUNTFOR),0) fcurwrittenoffamountfor,ISNULL(sum(data.FCURWRITTENOFFAMOUNT),0) fcurwrittenoffamount FROM (SELECT LogEntry.FSourceFromid,FSrcBillId,LogEntry.FSRCROWID,LogEntry.FCURWRITTENOFFAMOUNTFOR,LogEntry.FCURWRITTENOFFAMOUNT FROM T_AR_RECMacthLog PayLog INNER JOIN T_AR_RECMacthLogENTRY LogEntry ON PayLog.Fid = LogEntry.Fid WHERE LogEntry.FSourceFromid = 'AR_REFUNDBILL') data GROUP BY data.FSourceFromid,data.FSrcBillId,data.FSRCROWID) t17 ON t1.FId = t17.FSrcBillId AND t2.FENTRYID = t17.FSRCROWIDwhere t1.FDOCUMENTSTATUS='C' and t11.FNAME<>'关联公司'group by t5.FNAME,t4.FNAME,t1.FDATE,t1.FCURRENCYIDunion allselect t5.FNAME 组织,t4.FNAME 客户,SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) 应收金额,case when datediff(dd,t1.FENDDATE,getdate())<=0 then SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [账期内],case when datediff(dd,t1.FENDDATE,getdate())>0 and datediff(dd,t1.FENDDATE,getdate())<=30 then SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期1个月],case when datediff(dd,t1.FENDDATE,getdate())>30 and datediff(dd,t1.FENDDATE,getdate())<=60 then SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期2个月],case when datediff(dd,t1.FENDDATE,getdate())>60 and datediff(dd,t1.FENDDATE,getdate())<=90 then SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期3个月],case when datediff(dd,t1.FENDDATE,getdate())>90 then SUM(ISNULL(t2.FAMOUNTFOR,0)-ISNULL(t17.FCURWRITTENOFFAMOUNTFOR,0)) end [超账期4个月及以上],t1.FCURRENCYID 币别from T_AR_OtherRecAble t1left join T_AR_OtherRecAbleENTRY t2 on t1.FID=t2.FIDleft join T_BD_CUSTOMER_L t4 on t4.FCUSTID=t1.FCONTACTUNIT and t4.FLOCALEID=2052left join T_ORG_ORGANIZATIONS_L t5 on t1.FSALEORGID=t5.FORGID and t5.FLOCALEID=2052left join T_BD_CUSTOMER t10 on t4.FCUSTID=t10.FCUSTIDleft join T_BD_CUSTOMERGROUP_L t11 on t10.FPRIMARYGROUP=t11.FID and t11.FLOCALEID=2052left join (SELECT data.FSourceFromid,data.FSrcBillId,data.FSRCROWID,ISNULL(sum(data.FCURWRITTENOFFAMOUNTFOR),0) fcurwrittenoffamountfor,ISNULL(sum(data.FCURWRITTENOFFAMOUNT),0) fcurwrittenoffamount FROM (SELECT LogEntry.FSourceFromid,FSrcBillId,LogEntry.FSRCROWID,LogEntry.FCURWRITTENOFFAMOUNTFOR,LogEntry.FCURWRITTENOFFAMOUNT FROM T_AR_RECMacthLog PayLog INNER JOIN T_AR_RECMacthLogENTRY LogEntry ON PayLog.Fid = LogEntry.Fid WHERE LogEntry.FSourceFromid = 'AR_OtherRecAble') data GROUP BY data.FSourceFromid,data.FSrcBillId,data.FSRCROWID) t17 ON t1.FId = t17.FSrcBillId AND t2.FENTRYID = t17.FSRCROWIDwhere t1.FDOCUMENTSTATUS='C' and t11.FNAME<>'关联公司'group by t5.FNAME,t4.FNAME,t1.FENDDATE,t1.FCURRENCYID)tleft join T_BD_RATE m on t.币别=m.FCYFORID and m.FRATETYPEID=2 where t.组织='公司名称'group by t.组织,t.客户,t.币别
如果觉得《金蝶Cloud 应收账龄分析表-按到期日查询SQL》对你有帮助，请点赞、收藏，并留下你的观点哦！
