从多个行中获取带有分组日期的值的总和

2020-09-03 09:14发布

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


如果我没有在Group By子句中引入 RefDate 字段,则下面的查询将正常工作。 基本上,这是一个应计查询,我们在每个月末运行以获取所有已过帐交易的总和,目的是将成本从资产转移到销售成本。 通过在整个财务期间从贷方和借方获得差额的总值, SUM 函数可以很好地工作,但是如果我引入OJDT.RefDate可以限制基于交易日期查看的交易 ,由于RefDate分组, SUM 函数将在该期间内发布的每笔交易进行汇总。

我想要的查询是忽略Group By RefDate ,但仍要考虑HAVING子句中Date的限制。 如何重写查询以避免RefDate对结果进行分组,但仍具有限制交易日期的功能?

 SELECT DISTINCT T1。[帐户]'帐户代码','USD''货币',''借方',CAST(-SUM(T1。[SYSCred]-T1。[SYSDeb])AS VARCHAR)'Credit  ',T2。[AcctName]'备注',''Ref1',''Ref2','''VAT/GST代码',
 T0。[DocNum]"项目"," kur","区域"," RF","部门"," N/A-TRKS","卡车/设备"," N/A-TRL","拖车"," AJC"  "法律实体","坦桑尼亚"," U_geography",T0。[U_Global_ID]," U_ZEDS_TransactionId",T3。[RefDate]"交易记录"。 日期'
 从[@TRIP_H] T0
 内部联接[JDT1] T1在T1上。[项目] = CAST(T0。[DocNum] AS nvarchar)
 内部联接[OACT] T2在T2上。[AcctCode] = T1。[Account]
 内联接[OJDT] T3在T3上。[TransId] = T1。[TransId]
 GROUP BY T0。[DocNum],T0。[U_Trip_Status],T0。[U_Global_ID],T1。[Account],T2。[AcctName],T2。[FatherNum],T3。[RefDate]
 拥有T0。[U_Trip_Status] IN('4')和LEFT(T1。[Account],3)IN('143')和T1。[Account]!='143002'AND T2。[FatherNum] ='143042'  AND T3。[RefDate] <='2019-07-31 00:00:00.000'AND SUM(T1。[SYSCred]-T1。[SYSDeb])!= 0




 全联盟


 SELECT DISTINCT(案例T1。[帐户]当143043 THEN'500000'当143044 THEN'500055'当143046 THEN'500080'当143047 THEN'500085'当143048 THEN'500087'当143049 THEN'500089'当143051 THEN'500091  '
 当143052 THEN'500092'当143053 THEN'500095'当143054 THEN'500194'当143055 THEN'510160'当143056 THEN'500096'当143072 THEN'500097'当143057 THEN'510162'当143061 THEN'500093'
 当143058 THEN'500094'当143059 THEN'500090'当143062 THEN'510170'当143063 THEN'500122'当143064 THEN'500195'当143065 THEN'500086'当143066 THEN'500088'当143067 THEN'510050'
 当143073 THEN'500124'当143068 THEN'500070'当143070 THEN'500065'当143071 THEN'500060'当143069 THEN'500050'END)'帐户代码',
 'USD''Currency',CAST(-SUM(T1。[SYSCred]-T1。[SYSDeb])AS VARCHAR)'Debit',''Credit',T2。[AcctName]'Remarks',''Ref1  ','''Ref2','''VAT/GST代码',T0。[DocNum]'项目','kur''地区','RF''部门',
 " N/A-TRKS","卡车/设备"," N/A-TRL","拖车"," AJC","法律实体","坦桑尼亚"," U_geography",T0。[U_Global_ID]'U_ZEDS_TransactionId',T3。  [RefDate]'交易。 日期'
 从[@TRIP_H] T0
 内部联接[JDT1] T1在T1上。[项目] = CAST(T0。[DocNum] AS nvarchar)
 内部联接[OACT] T2在T2上。[AcctCode] = T1。[Account]
 内联接[OJDT] T3在T3上。[TransId] = T1。[TransId]
 GROUP BY T0。[DocNum],T0。[U_Trip_Status],T0。[U_Global_ID],T1。[Account],T2。[AcctName],T2。[FatherNum],T3。[RefDate]
 拥有T0。[U_Trip_Status] IN('4')和LEFT(T1。[Account],3)IN('143')和T1。[Account]!='143002'AND T2。[FatherNum] ='143042'  AND T3。[RefDate] <='2019-07-31 00:00:00.000'AND SUM(T1。[SYSCred]-T1。[SYSDeb])!= 0


 按T1。[帐户],T0。[DocNum] DESC排序
 

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


如果我没有在Group By子句中引入 RefDate 字段,则下面的查询将正常工作。 基本上,这是一个应计查询,我们在每个月末运行以获取所有已过帐交易的总和,目的是将成本从资产转移到销售成本。 通过在整个财务期间从贷方和借方获得差额的总值, SUM 函数可以很好地工作,但是如果我引入OJDT.RefDate可以限制基于交易日期查看的交易 ,由于RefDate分组, SUM 函数将在该期间内发布的每笔交易进行汇总。

我想要的查询是忽略Group By RefDate ,但仍要考虑HAVING子句中Date的限制。 如何重写查询以避免RefDate对结果进行分组,但仍具有限制交易日期的功能?

 SELECT DISTINCT T1。[帐户]'帐户代码','USD''货币',''借方',CAST(-SUM(T1。[SYSCred]-T1。[SYSDeb])AS VARCHAR)'Credit  ',T2。[AcctName]'备注',''Ref1',''Ref2','''VAT/GST代码',
 T0。[DocNum]"项目"," kur","区域"," RF","部门"," N/A-TRKS","卡车/设备"," N/A-TRL","拖车"," AJC"  "法律实体","坦桑尼亚"," U_geography",T0。[U_Global_ID]," U_ZEDS_TransactionId",T3。[RefDate]"交易记录"。 日期'
 从[@TRIP_H] T0
 内部联接[JDT1] T1在T1上。[项目] = CAST(T0。[DocNum] AS nvarchar)
 内部联接[OACT] T2在T2上。[AcctCode] = T1。[Account]
 内联接[OJDT] T3在T3上。[TransId] = T1。[TransId]
 GROUP BY T0。[DocNum],T0。[U_Trip_Status],T0。[U_Global_ID],T1。[Account],T2。[AcctName],T2。[FatherNum],T3。[RefDate]
 拥有T0。[U_Trip_Status] IN('4')和LEFT(T1。[Account],3)IN('143')和T1。[Account]!='143002'AND T2。[FatherNum] ='143042'  AND T3。[RefDate] <='2019-07-31 00:00:00.000'AND SUM(T1。[SYSCred]-T1。[SYSDeb])!= 0




 全联盟


 SELECT DISTINCT(案例T1。[帐户]当143043 THEN'500000'当143044 THEN'500055'当143046 THEN'500080'当143047 THEN'500085'当143048 THEN'500087'当143049 THEN'500089'当143051 THEN'500091  '
 当143052 THEN'500092'当143053 THEN'500095'当143054 THEN'500194'当143055 THEN'510160'当143056 THEN'500096'当143072 THEN'500097'当143057 THEN'510162'当143061 THEN'500093'
 当143058 THEN'500094'当143059 THEN'500090'当143062 THEN'510170'当143063 THEN'500122'当143064 THEN'500195'当143065 THEN'500086'当143066 THEN'500088'当143067 THEN'510050'
 当143073 THEN'500124'当143068 THEN'500070'当143070 THEN'500065'当143071 THEN'500060'当143069 THEN'500050'END)'帐户代码',
 'USD''Currency',CAST(-SUM(T1。[SYSCred]-T1。[SYSDeb])AS VARCHAR)'Debit',''Credit',T2。[AcctName]'Remarks',''Ref1  ','''Ref2','''VAT/GST代码',T0。[DocNum]'项目','kur''地区','RF''部门',
 " N/A-TRKS","卡车/设备"," N/A-TRL","拖车"," AJC","法律实体","坦桑尼亚"," U_geography",T0。[U_Global_ID]'U_ZEDS_TransactionId',T3。  [RefDate]'交易。 日期'
 从[@TRIP_H] T0
 内部联接[JDT1] T1在T1上。[项目] = CAST(T0。[DocNum] AS nvarchar)
 内部联接[OACT] T2在T2上。[AcctCode] = T1。[Account]
 内联接[OJDT] T3在T3上。[TransId] = T1。[TransId]
 GROUP BY T0。[DocNum],T0。[U_Trip_Status],T0。[U_Global_ID],T1。[Account],T2。[AcctName],T2。[FatherNum],T3。[RefDate]
 拥有T0。[U_Trip_Status] IN('4')和LEFT(T1。[Account],3)IN('143')和T1。[Account]!='143002'AND T2。[FatherNum] ='143042'  AND T3。[RefDate] <='2019-07-31 00:00:00.000'AND SUM(T1。[SYSCred]-T1。[SYSDeb])!= 0


 按T1。[帐户],T0。[DocNum] DESC排序
 
付费偷看设置
发送
2条回答
半个程序猿
1楼 · 2020-09-03 10:18.采纳回答

如果您不联接整个OJDT表而是表的一部分,该怎么办? 类似于以下内容:

这里是从FROM到UNION ALL的部分的改写

我自己不是SQL方面的专家,因此不能保证此方法有效。 只是一个想法。 希望能帮助到你。

从FROM [@TRIP_H] T0
 内接头[JDT1] T1
     开启T1。[项目] = CAST(T0。[DocNum] AS NVARCHAR)
 内联接[OACT] T2
     开启T2。[AcctCode] = T1。[Account]
 内部联接 (
     选择 *
     来自[OJDT]
     在哪里[RefDate] <='2019-07-31 00:00:00.000'
     T3
     开启T3。[TransId] = T1。[TransId]
 T0分组。[DocNum]
     ,T0。[U_Trip_Status]
     ,T0。[U_Global_ID]
     ,T1。[帐户]
     ,T2。[AcctName]
     ,T2。[FatherNum]
 拥有T0。[U_Trip_Status] IN('4')
     AND LEFT(T1。[Account],3)IN('143')
     AND T1。[帐户]!='143002'
     AND T2。[FatherNum] ='143042'
     AND SUM(T1。[SYSCred]-T1。[SYSDeb])!= 0
 
黑丝骑士
2楼-- · 2020-09-03 09:54

感谢您分享您的输入,它对您有所帮助。 但是我最终使用了与表[@TRIP_H]不同的日期字段,这给了我我想要的东西。

您的解决方案在一定程度上有效,但是需要注意的其他条件也会增加结果的数量。

再次感谢。

一周热门 更多>