如何在此查询中添加文档总数列?

2020-09-30 01:42发布

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

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


选择

T0.DocNum AS'发票编号',
T0.DocStatus,T0.Jrnlmemo,
T0.NumAtCard AS'客户参考编号',

T1.SlpName AS'销售人员',

-T0.SlpCode AS'SP代码',

T0.DocDate AS"发布日期",

T0.CardCode AS'客户代码'

T0.CardName AS'CustomerName',

T0.Address2 AS'ShipToAddress'

" Credit Memos" AS"文档类型",

(((T0.DocTotal)* -1)AS'总计文档',

(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)AS'产品销售',

(((T0.GrosProfit)* -1)AS'毛利润',

案例

当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00 THEN 0.00

ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100

以"毛利润%"结尾,

(((T0.PaidToDate)* -1)AS'Paid To Date',

(((T0.DocTotal-T0.PaidToDate)* -1)AS'在文档上打开'

FROM ORIN T0

内部联接OSLP T1

打开T0.SlpCode = T1.SlpCode

在哪里

(T1.SlpName LIKE'%% [%0] %%'或'[%0]'='')

AND T0.TaxDate> ='[%1]'

AND T0.TaxDate <='[%2]'

UNION

选择

T0.DocNum AS'发票编号',
T0.DocStatus,T0.Jrnlmemo,
T0.NumAtCard AS'客户参考编号',

T1.SlpName AS'销售人员',

-T0.SlpCode AS'SP代码',

T0.DocDate AS"发布日期",

T0.CardCode AS'客户代码'

T0.CardName AS'CustomerName',

T0.Address2 AS'ShipToAddress'

"发票" AS"文档类型",

(T0.DocTotal)作为"文档总数",

(((T0.DocTotal-T0.VatSum)-T0.TotalExpns)作为"产品销售",

(T0.GrosProfit)AS'毛利润',

案例

当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns))= 0.00 THEN 0.00

ELSE((T0.GrosProfit)/(((T0.DocTotal-T0.VatSum)-T0.TotalExpns))* 100

以"毛利润%"结尾,

(T0.PaidToDate)AS'Paid To Date',

(T0.DocTotal-T0.PaidToDate)为"在文档上打开"

从OINV T0

内部联接OSLP T1

打开T0.SlpCode = T1.SlpCode

在哪里

(T1.SlpName LIKE'%% [%0] %%'或'[%0]'='')

AND T0.TaxDate> ='[%1]'

AND T0.TaxDate <='[%2]'

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

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


选择

T0.DocNum AS'发票编号',
T0.DocStatus,T0.Jrnlmemo,
T0.NumAtCard AS'客户参考编号',

T1.SlpName AS'销售人员',

-T0.SlpCode AS'SP代码',

T0.DocDate AS"发布日期",

T0.CardCode AS'客户代码'

T0.CardName AS'CustomerName',

T0.Address2 AS'ShipToAddress'

" Credit Memos" AS"文档类型",

(((T0.DocTotal)* -1)AS'总计文档',

(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)AS'产品销售',

(((T0.GrosProfit)* -1)AS'毛利润',

案例

当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00 THEN 0.00

ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100

以"毛利润%"结尾,

(((T0.PaidToDate)* -1)AS'Paid To Date',

(((T0.DocTotal-T0.PaidToDate)* -1)AS'在文档上打开'

FROM ORIN T0

内部联接OSLP T1

打开T0.SlpCode = T1.SlpCode

在哪里

(T1.SlpName LIKE'%% [%0] %%'或'[%0]'='')

AND T0.TaxDate> ='[%1]'

AND T0.TaxDate <='[%2]'

UNION

选择

T0.DocNum AS'发票编号',
T0.DocStatus,T0.Jrnlmemo,
T0.NumAtCard AS'客户参考编号',

T1.SlpName AS'销售人员',

-T0.SlpCode AS'SP代码',

T0.DocDate AS"发布日期",

T0.CardCode AS'客户代码'

T0.CardName AS'CustomerName',

T0.Address2 AS'ShipToAddress'

"发票" AS"文档类型",

(T0.DocTotal)作为"文档总数",

(((T0.DocTotal-T0.VatSum)-T0.TotalExpns)作为"产品销售",

(T0.GrosProfit)AS'毛利润',

案例

当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns))= 0.00 THEN 0.00

ELSE((T0.GrosProfit)/(((T0.DocTotal-T0.VatSum)-T0.TotalExpns))* 100

以"毛利润%"结尾,

(T0.PaidToDate)AS'Paid To Date',

(T0.DocTotal-T0.PaidToDate)为"在文档上打开"

从OINV T0

内部联接OSLP T1

打开T0.SlpCode = T1.SlpCode

在哪里

(T1.SlpName LIKE'%% [%0] %%'或'[%0]'='')

AND T0.TaxDate> ='[%1]'

AND T0.TaxDate <='[%2]'

付费偷看设置
发送
6条回答
天桥码农
1楼 · 2020-09-30 02:04.采纳回答

贾斯汀,您好,

请注意,由于Nagarajan并未格式化其代码,因此与原始查询相比,该解决方案的大部分被注释掉了。 通常,最好使用/*注释*/语法,而不是-注释语法。

请尝试对 Nagarajan的查询:

 SELECT T0.DocNum AS'Credit Nr'
      ,T0.DocStatus
  ,T0.Jrnlmemo
  ,T0.NumAtCard AS'Customer Ref Nr'
  ,T1.SlpName AS'销售人员'
  ,T0.DocDate AS'发布日期'
  ,T0.CardCode AS'客户代码'
  ,T0.CardName AS'CustomerName'
  ,T0.Address2 AS'ShipToAddress'
  ," Credit Memos"和" Doc Type"
  ,((T0.DocTotal)* -1)AS'总文档数'
  ,(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)作为'产品销售'
  ,((T0.GrosProfit)* -1)AS'毛利润'
  , 案件
         当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00然后0.00
         ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100
        以"毛利润百分比"结尾
      ,((T0.PaidToDate)* -1)AS'Paid To Date'
      ,(((T0.DocTotal-T0.PaidToDate)* -1)AS'在文档上打开'
  ,SUM(T2.Quantity)为TotalQty
 从ORIN T0
      内连接OSLP T1开启T0.SlpCode = T1.SlpCode
  T2上的INNER JOIN RIN1 T2.Docentry = T0.Docentry
 在哪里(T1.SlpName喜欢'%% [%0] %%'或'[%0]'='')
   AND T0.TaxDate> ='[%1]'
   AND T0.TaxDate <='[%2]'
 GROUP BY T0.Docnum,T0.DocStatus,T0.Jrnlmemo,T0.NumAtCard,T1.SlpName,T0.DocDate,T0.CardCode,T0.CardName,T0.Address2,T0.DocTotal,T0.VatSum,T0.TotalExpns,  T0.GrosProfit,T0.PaidToDate
 全部合并
 选择T0.DocNum AS'发票Nr'
      ,T0.DocStatus
  ,T0.Jrnlmemo
  ,T0.NumAtCard AS'Customer Ref Nr'
  ,T1.SlpName AS'销售人员'
  ,T0.DocDate AS'发布日期'
  ,T0.CardCode AS'客户代码'
  ,T0.CardName AS'CustomerName'
  ,T0.Address2 AS'ShipToAddress'
  ,"发票"和"文档类型"
  ,((T0.DocTotal)* -1)AS'总文档数'
  ,(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)作为'产品销售'
  ,((T0.GrosProfit)* -1)AS'毛利润'
  , 案件
     当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00然后0.00
 ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100
    以"毛利润百分比"结尾
  ,((T0.PaidToDate)* -1)AS'Paid To Date'
  ,(((T0.DocTotal-T0.PaidToDate)* -1)AS'在文档上打开'
  ,-SUM(T2.Quantity)作为TotalQty
 从OINV T0
      内连接OSLP T1开启T0.SlpCode = T1.SlpCode
  T2上的INNER JOIN INV1 T2.Docentry = T0.Docentry
 其中(T1.SlpName喜欢'%% [%0] %%'或'[%0]'='')
   AND T0.TaxDate> ='[%1]'
   AND T0.TaxDate <='[%2]'
 按T0.Docnum,T0.DocStatus,T0.Jrnlmemo,T0.NumAtCard,T1.SlpName,T0.DocDate,T0.CardCode,T0.CardName,T0.Address2,T0.DocTotal,T0.VatSum,T0.TotalExpns,  T0.GrosProfit,T0.PaidToDate 

问候,

Johan

大道至简
2楼-- · 2020-09-30 01:48

嗨,

忽略以上查询。 试试这个,

SELECT T0.DocNum AS'Credit Nr', T0.DocStatus,T0.Jrnlmemo, T0.NumAtCard AS'Customer Ref Nr', T1.SlpName AS'销售人员', --T0.SlpCode AS'SP代码', T0.DocDate AS"发布日期", T0.CardCode AS'客户代码', T0.CardName AS'CustomerName', T0.Address2 AS'ShipToAddress', " Credit Memos" AS"文档类型", ((T0.DocTotal)* -1)作为'Total Docs', ((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)作为'产品销售', ((T0.GrosProfit)* -1)AS'毛利润', 案件 当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00然后0.00 ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100 以"毛利润百分比"结尾, ((T0.PaidToDate)* -1)AS'Paid To Date', (((T0.DocTotal-T0.PaidToDate)* -1)AS'Open on Docs',SUM(T2.Quantity)as TotalQty

从ORIN T0 内部联接OSLP T1 开启T0.SlpCode = T1.SlpCode INNER JOIN RIN1 T2在T2.Docentry = T0.Docentry

其中 (T1.SlpName喜欢'%% [%0] %%'或'[%0]'='')AND T0.TaxDate> ='[%1]'AND T0.TaxDate <='[%2]'

按T0.Docnum,T0.DocStatus,T0.Jrnlmemo,T0.NumAtCard,T1.SlpName,T0.DocDate,T0.CardCode,T0.CardName,T0.Address2,T0.DocTotal,T0分组 .VatSum,T0.TotalExpns,T0.GrosProfit,T0.PaidToDate

全部联合

SELECT T0.DocNum AS'Invoice Nr', T0.DocStatus,T0.Jrnlmemo, T0.NumAtCard AS'Customer Ref Nr', T1.SlpName AS'销售人员', --T0.SlpCode AS'SP代码', T0.DocDate AS"发布日期", T0.CardCode AS'客户代码', T0.CardName AS'CustomerName', T0.Address2 AS'ShipToAddress', " Credit Memos" AS"文档类型", ((T0.DocTotal)* -1)作为'Total Docs', ((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)作为'产品销售', ((T0.GrosProfit)* -1)AS'毛利润', 案件 当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00然后0.00 ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100 以"毛利润百分比"结尾, ((T0.PaidToDate)* -1)AS'Paid To Date', ((T0.DocTotal-T0.PaidToDate)* -1)AS'Open on Docs',-SUM(T2.Quantity)as TotalQty

FROM OINV T0 内部联接OSLP T1 开启T0.SlpCode = T1.SlpCode INNER JOIN INV1 T2上的T2.Docentry = T0.Docentry

其中 (T1.SlpName喜欢'%% [%0] %%'或'[%0]'='')AND T0.TaxDate> ='[%1]'AND T0.TaxDate <='[%2]'

按T0.Docnum,T0.DocStatus,T0.Jrnlmemo,T0.NumAtCard,T1.SlpName,T0.DocDate,T0.CardCode,T0.CardName,T0.Address2,T0.DocTotal,T0分组 .VatSum,T0.TotalExpns,T0.GrosProfit,T0.PaidToDate

宇峰
3楼-- · 2020-09-30 02:06

嗨,

尝试一下,

选择 T0.DocNum AS'Invoice Nr', T0.DocStatus,T0.Jrnlmemo, T0.NumAtCard AS'Customer Ref Nr', T1.SlpName AS'销售人员', --T0.SlpCode AS'SP代码', T0.DocDate AS"发布日期", T0.CardCode AS'客户代码', T0.CardName AS'CustomerName', T0.Address2 AS'ShipToAddress', " Credit Memos" AS"文档类型", ((T0.DocTotal)* -1)作为'Total Docs', ((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)作为'产品销售', ((T0.GrosProfit)* -1)AS'毛利润', 案件 当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1)= 0.00然后0.00 ELSE((((T0.GrosProfit)* -1)/(((((T0.DocTotal-T0.VatSum)-T0.TotalExpns)* -1))* 100 以"毛利润百分比"结尾, ((T0.PaidToDate)* -1)AS'Paid To Date', ((T0.DocTotal-T0.PaidToDate)* -1)AS'在文档上打开', SUM(T2.Quantity]作为TotalQty 从ORIN T0内部连接T0.Docentry = T2.Docentry上的RIN1 T2 内部联接OSLP T1 开启T0.SlpCode = T1.SlpCode 哪里 (T1.SlpName之类的'%% [%0] %%'或'[%0]'='') AND T0.TaxDate> ='[%1]' AND T0.TaxDate <='[%2]'

UNION

SELECT T0.DocNum AS'Invoice Nr', T0.DocStatus,T0.Jrnlmemo, T0.NumAtCard AS'Customer Ref Nr', T1.SlpName AS'销售人员', --T0.SlpCode AS'SP代码', T0.DocDate AS"发布日期", T0.CardCode AS'客户代码', T0.CardName AS'CustomerName', T0.Address2 AS'ShipToAddress', "发票" AS"文档类型", (T0.DocTotal)作为"文档总数", ((T0.DocTotal-T0.VatSum)-T0.TotalExpns)作为"产品销售", (T0.GrosProfit)作为"毛利润", 案件 当((((T0.DocTotal-T0.VatSum)-T0.TotalExpns))= 0.00然后0.00 ELSE((T0.GrosProfit)/(((T0.DocTotal-T0.VatSum)-T0.TotalExpns))* 100 以"毛利润百分比"结尾, (T0.PaidToDate)作为"迄今付款", (T0.DocTotal-T0.PaidToDate)作为"在文档上打开", SUM(T2.Quantity]作为TotalQty 从OINV T0内部联接T0.Docentry = T2.Docentry上的INV1 T2 内部联接OSLP T1 开启T0.SlpCode = T1.SlpCode 哪里 (T1.SlpName之类的'%% [%0] %%'或'[%0]'='') AND T0.TaxDate> ='[%1]' AND T0.TaxDate <='[%2]'

分组依据 T0.DocNum T0.DocStatus,T0.Jrnlmemo, T0.NumAtCard, T1.SlpName, T0.DocDate, T0.CardCode, T0.CardName, T0.Address2, T0.DocTotal, T0.VatSum,T0.TotalExpns, T0.GrosProfit, T0.PaidToDate

落灬小鱼
5楼-- · 2020-09-30 02:12

尊敬的Johan和Nagarajan

我相信我们非常感谢你们俩的帮助。

最好的问候

Justin

风早神人
6楼-- · 2020-09-30 02:03

尊敬的Johan和Nagarajan

我相信我们非常感谢你们俩的帮助。

最好的问候

Justin

一周热门 更多>