点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)专家们 Am有一个针对客...
点击此处---> 群内免费提供SAP练习系统(在群公告中)加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)专家们 Am有一个针对客...
加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)
专家们
Am有一个针对客户老化的自定义报告的sql查询
它没有按照标准账龄报告向我返回正确的值
我为我的客户使用多币种,但需要以本地货币进行帐龄
我有一些领域
我还需要使用GlLicnumber过滤查询(在该查询中,我已经给了集合响应人名称)
任何人都可以帮助我编辑此查询并帮助我对其进行整理
致谢
SELECT tt。[Cust Num], tt。[客户名称], tt。[负责收款], tt。[余额], tt。[GroupCode], --tt。[上次收到付款], -tt。[上次收到付款日期], tt。[PymntGroup], --tt。[AliasName], tt。[city], --SUM(tt。[Credit Amt])AS [Credit Amt], SUM(tt。[0-30天])AS [0-30天], SUM(tt。[31至60天])AS [31至60天], SUM(tt。[61至90天])AS [61至90天], SUM(tt。[90至120天])AS [91至120天], SUM(tt。[121 Plus天])AS [121 Plus天] 来自( SELECT T1.CardCode AS'Cust Num', T1.CardName AS'客户名称', T1.GlblLocNum AS"负责收集", T0.BalDueDeb AS'Debit Amt', T0.BalDueCred * -1 AS'Credit Amt', T1.Balance AS" Balance", T1。[GroupCode], T2。[PymntGroup], --T1。[AliasName], T3。[城市], IsNull((从ORCT中选择前1个总和(DocTotal),其中ORCT.CardCode = T1.CardCode和ORCT.Status <>'C'和(ORCT.DocDate> = ORCT.DocDate和ORCT.DocDate <= ORCT.DocDate)组 通过ORCT.DocDate排序通过ORCT.DocDate DESC),0)'收到的最后付款', IsNull((从ORCT中选择前1个最大(DocDate),其中ORCT.CardCode = T1.CardCode和ORCT.Status <>'C'和(ORCT.DocDate> = ORCT.DocDate和ORCT.DocDate <= ORCT.DocDate)组 通过ORCT.DocDate排序通过ORCT.DocDate DESC),0)'最后一次收到付款日期', 案件 当T0.TransType = 13 THEN'AR Invoice' 当T0.TransType = 14时'AR Cred Memo' 当T0.TransType = 24然后"付款" ELSE"其他" END AS"转换类型", T0.Ref1 AS'参考', FcCurrency AS'货币', CONVERT(VARCHAR(10),RefDate,103)'过帐日期', CONVERT(VARCHAR(10),DueDate,103)'到期日', CONVERT(VARCHAR(10),TaxDate,103)'文档日期', 案件 当(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <30 THEN CASE 什么时候 Balduecred < > 0然后 Balduecred * -- 1个 其他 巴尔杜德 结束 以" 0-30天"结束, 案件 时间(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1> 30 AND(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <61 THEN 案件 当balduecred <> 0然后balduecred * -1 艾尔兹·巴尔杜德 结束 结束于" 31至60天", 案件 时间(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1> 60 AND(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <91 THEN 案件 当balduecred <> 0然后balduecred * -1 艾尔兹·巴尔杜德 结束 结束为" 61至90天", 案件 时间(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1> 90 AND(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <121 THEN 案件 当balduecred <> 0然后balduecred * -1 艾尔兹·巴尔杜德 结束 结束为" 90至120天", 案件 时间(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1> 121 THEN CASE 什么时候 Balduecred < > 0然后 Balduecred * -- 1个 其他 巴尔杜德 结束 结束于" 121天以上" 从JDT1 T0 内连接OCRD T1 开启T0.ShortName = T1.CardCode AND T1.CardType ='C'/* AND T1.QryGroup1 ='Y'*/ 内部联接OCTG T2 ON T1.GroupNum = T2.GroupNum 左外连接CRD1 T3在T1.City = T3.City 哪里 T1.CardType ='C'/* AND T1.CARDCODE NOT IN('DB00000063','JB00000145','RB00000465')*/AND T0.IntrnMatch ='0' AND T0.BalDueDeb!= T0.BalDueCred AND T1.Balance <>'0'/*和t1.CardCode ='RB0000117'*/和T1.GlblLocNum ='SAFDAR' )AS TT 通过...分组 tt。[Cust Num], tt。[客户名称], tt。[余额], tt。[GroupCode], tt。[上次收到的付款], tt。[上次收到付款日期], tt。[负责收款], tt。[PymntGroup], --tt。[AliasName] tt。[CITY] 按tt排序。[客户名称]
嗨,
尝试此查询。 您的OCRD和CRD1表之间的查询链接中的问题
SELECT tt。[客户编号],tt。[客户名称],tt。[负责收款],tt。[余额],tt。[GroupCode],-tt。[收到的最后付款],- tt。[上次收到付款的日期],tt。[PymntGroup],-tt。[AliasName],tt。[city],-SUM(tt。[Credit Amt])AS [Credit Amt],SUM(tt。 [0-30天])AS [0-30天],SUM(tt。[31至60天])AS [31到60天],SUM(tt。[61至90天])AS [61至90 天],SUM(tt。[90到120天])AS [91到120天],SUM(tt。[121加上天])AS [121加上天]
从(SELECT T1.CardCode AS'Cust Num',T1.CardName AS'Cust Name',T1.GlblLocNum AS'Responsible to Collection',T0.BalDueDeb AS'Debit Amt',T0.BalDueCred * -1 AS 'Credit Amt',T1.Balance AS'Balance',T1。[GroupCode],T2。[PymntGroup],--T1。[AliasName],T3。[City],IsNull((选择前1个Sum(DocTotal)从 ORCT其中ORCT.CardCode = T1.CardCode和ORCT.Status <>'C'和(ORCT.DocDate> = ORCT.DocDate和ORCT.DocDate <= ORCT.DocDate)按ORCT.DocDate按ORCT.DocDate DESC排序 ,0)'收到最后付款',IsNull((从ORCT中选择前1个最大(DocDate),其中ORCT.CardCode = T1.CardCode和ORCT.Status <>'C'和(ORCT.DocDate> = ORCT.DocDate和ORCT .DocDate <= ORCT.DocDate)按ORCT.DocDate分组,按ORCT.DocDate DESC),0)'最后收到的付款日期',当T0.TransType = 13 THEN时为案例"当T0.TransType = 14 THEN时为AR发票" AR Cred Memo',当T0.TransType = 24然后'Payment'ELSE'Other'END AS'Trans Type',T0.Ref1 AS'Reference',FcCurrency AS'Currency',CONVERT(VARCHAR(10),RefD 日期,103)"发布日期",CONVERT(VARCHAR(10),到期日期,103)"到期日",CONVERT(VARCHAR(10),TaxDate,103)"文档日期",当(DATEDIFF(DD,RefDate, CURRENT_TIMESTAMP))+ 1 <30当balduecred时出现<> 0 THEN balduedeb *-1 ELSE balduedeb END END AS'0-30 Days',当(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1> 30 AND(DATEDIFF) (DD,RefDate,CURRENT_TIMESTAMP))+ 1 <61当balduecred <> 0 THEN balduedred时* 1 ELSE balduedeb END END AS '31 to 60 Days',CASE WHEN(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 > 60 AND(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <91当balduecred时为例<> 0 THEN balduecred * -1 ELSE balduedeb END END AS '61至90天',情况为(DATEDIFF(DD,RefDate, CURRENT_TIMESTAMP))+ 1> 90 AND(DATEDIFF(DD,RefDate,CURRENT_TIMESTAMP))+ 1 <121然后在balduecred <> 0的情况下将其打包* -1 ELSE balduedeb END END AS '90至120天'(在DATEDIFF时) (DD,RefDate,CURRENT_TIMESTAMP))+ 1> 121 duecred <> 0 THEN balduecred *-1 ELSE balduedeb END END AS'121 Plus Days'
来自JDT1 T0内连接OCRD T1开启T0.ShortName = T1.CardCode AND T1.CardType ='C'/* AND T1.QryGroup1 ='Y'*/内部连接OCTG T2开启T1.GroupNum = T2.GroupNum 内部联接CRD1 T3开启T1.cardcode = T3.cardcode和T1。[BillToDef] = T3。[地址] T1.CardType ='C'/* AND T1.CARDCODE NOT IN('DB00000063','JB00000145',' RB00000465')*/AND T0.IntrnMatch ='0'AND T0.BalDueDeb!= T0.BalDueCred AND T1.Balance <>'0'-/*和t1.CardCode ='RB0000117'*/和T1.GlblLocNum = 'SAFDAR')AS tt
GROUP BY tt。[客户编号],tt。[客户名称],tt。[余额],tt。[GroupCode],tt。[上次收到的付款],tt。[上次收到的付款日期],tt。 [负责收集],tt。[PymntGroup],-tt。[AliasName] tt。[CITY]按tt排序。[客户名称]
此致
Nagarajan
一周热门 更多>