Running total sql SAP B1 - Not found a proper way to resolve it

[Former Member]

I tried to have a running total row at the bottom of my table by running this following SQL code.

<code>SELECT
T1.[ItemCode],T4.[ItmsGrpNam],CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE
SUM(T1.[OpenQty])ENDAs'Missing Quantity'FROM ORDR T0
INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode
INNERJOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod

WHERE 
 T3.[WhsCode]= T1.[WhsCode]and  T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0UNIONALLSELECT'See','Total Missing Quantity',CASEWHEN T3.[OnHand]- SUM(T1.[OpenQty])< SUM(T1.[OpenQty])THEN T3.[OnHand]- SUM(T1.[OpenQty])ELSE
SUM(T1.[OpenQty])ENDFROM ORDR T0
INNERJOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
INNERJOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNERJOIN OITW T3 ON T2.ItemCode = T3.ItemCode
INNERJOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod

WHERE 
 T3.[WhsCode]= T1.[WhsCode]and  T0.[DocDueDate]<='[%D1]'and(T4.[ItmsGrpNam]='Commercial'Or T4.[ItmsGrpNam]='Residentiel')

I have this error message ' 1). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] The 'OITW.OnHand' column is not valid in the selection list because it is not contained in an aggregate function or in the clause BY GROUP. 2). [Microsoft] [SQL Server Native Client 11.0] [SQL Server] Can not prepare statements. 'User Defined Values' (CSHS)'

When I try to

<code>GROUPBY T1.[ItemCode],T3.[OnHand],T4.[ItmsGrpNam]HAVING T3.[OnHand]- SUM(T1.[OpenQty])<0Or SUM(T1.[OpenQty])<0

In the second part of the query, I get multiple rows when I only want one that sum all.

I spent the day to figure this out and there's multiple answers on the NET but I don't know what i'm doing wrong.

Does anyone can help me with this issue? Thanks in advance.

[chris_keating]

This issue does not involve a SQL Anywhere database. You should retag with more suitable tags.

[Former Member]

I'm sorry I will correct

[Johan_H · Accepted solution]

Hi Sébastien,

Please try this adapted version of your query:

SELECT T1.[ItemCode]
      ,T4.[ItmsGrpNam]
      ,CASE
        WHEN T3.[OnHand] - SUM(T1.[OpenQty]) < SUM(T1.[OpenQty]) THEN T3.[OnHand] - SUM(T1.[OpenQty])
        ELSE SUM(T1.[OpenQty])
       END AS [Missing Quantity]
FROM ORDR T0
     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
     INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
     INNER JOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod
WHERE T3.[WhsCode]= T1.[WhsCode]
  AND T0.[DocDueDate] <= '[%D1]'
  AND (T4.[ItmsGrpNam] = 'Commercial'
    or T4.[ItmsGrpNam] = 'Residentiel')
GROUP BY T1.[ItemCode]
        ,T3.[OnHand]
        ,T4.[ItmsGrpNam]
HAVING T3.[OnHand] - SUM(T1.[OpenQty]) < 0
    OR SUM(T1.[OpenQty]) < 0
UNION ALL
SELECT x.ItemCode
     , x.ItmsGrpNam
     , SUM(x.[Missing Quantity])
FROM (SELECT '' AS [ItemCode]
            ,'Zzee Total Missing Quantity' AS [ItmsGrpNam] /* A little poetic freedom to get the total at the bottom */
            ,CASE
              WHEN T3.[OnHand] - SUM(T1.[OpenQty]) < SUM(T1.[OpenQty]) THEN T3.[OnHand]- SUM(T1.[OpenQty])
              ELSE SUM(T1.[OpenQty])
             END AS [Missing Quantity]
      FROM ORDR T0
           INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
           INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
           INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
           INNER JOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod
      WHERE T3.[WhsCode] = T1.[WhsCode]
        AND T0.[DocDueDate] <= '[%D1]'
        AND (T4.[ItmsGrpNam] = 'Commercial'
          or T4.[ItmsGrpNam] = 'Residentiel')
      GROUP BY T3.[OnHand]
      HAVING T3.[OnHand] - SUM(T1.[OpenQty]) < 0
          OR SUM(T1.[OpenQty]) < 0) x
GROUP BY x.ItemCode
       , x.ItmsGrpNam
ORDER BY [ItmsGrpNam], [Missing Quantity]

Regards,

Johan

[Former Member · Accepted solution]

Hi Johan, thanks a lot for the reply.

Here's what I get with your adapted version of the code.

I think it's very close. I tried to modify it to have the good SUM at the bottom and have a row apart that doesn't take place over an existing ItemCode but it's giving me errors.

I'll try to figure this out later this morning.

Feel free to help me again it's very appreciated.

Thank you.

[Former Member · Accepted solution]

I put text to name the itemcode column and it correct the fact that the sum total row took place over an existing ItemCode.

FROM(SELECT'See'AS[ItemCode],'Zzee Total Missing Quantity'AS[ItmsGrpNam]/* A little poetic freedom toget the total at the bottom */

It left to correct the good total.

[Johan_H · Accepted solution]

Hi Sébastien,

I see that I made a mistake. Please try this:

SELECT T1.[ItemCode]
      ,T4.[ItmsGrpNam]
      ,CASE
        WHEN T3.[OnHand] - SUM(T1.[OpenQty]) < SUM(T1.[OpenQty]) THEN T3.[OnHand] - SUM(T1.[OpenQty])
        ELSE SUM(T1.[OpenQty])
       END AS [Missing Quantity]
FROM ORDR T0
     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
     INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
     INNER JOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod
WHERE T3.[WhsCode]= T1.[WhsCode]
  AND T0.[DocDueDate] <= '[%D1]'
  AND T4.[ItmsGrpNam] IN ('Commercial', 'Residentiel')
GROUP BY T1.[ItemCode]
        ,T3.[OnHand]
        ,T4.[ItmsGrpNam]
HAVING T3.[OnHand] - SUM(T1.[OpenQty]) < 0
    OR SUM(T1.[OpenQty]) < 0
UNION ALL
SELECT 'See'
     , 'zz Total Missing Quantity'
     , SUM(x.[Missing Quantity])
FROM (SELECT T1.[ItemCode]
      ,T4.[ItmsGrpNam]
      ,CASE
        WHEN T3.[OnHand] - SUM(T1.[OpenQty]) < SUM(T1.[OpenQty]) THEN T3.[OnHand] - SUM(T1.[OpenQty])
        ELSE SUM(T1.[OpenQty])
       END AS [Missing Quantity]
FROM ORDR T0
     INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry
     INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
     INNER JOIN OITW T3 ON T2.ItemCode = T3.ItemCode
     INNER JOIN OITB T4 ON  T2.ItmsGrpCod=T4.ItmsGrpCod
WHERE T3.[WhsCode]= T1.[WhsCode]
  AND T0.[DocDueDate] <= '[%D1]'
  AND T4.[ItmsGrpNam] IN ('Commercial', 'Residentiel')
GROUP BY T1.[ItemCode]
        ,T3.[OnHand]
        ,T4.[ItmsGrpNam]
HAVING T3.[OnHand] - SUM(T1.[OpenQty]) < 0
    OR SUM(T1.[OpenQty]) < 0) x
ORDER BY [ItmsGrpNam], [Missing Quantity]

Regards,

Johan

[Former Member · Accepted solution]

You're a genius. It worked perfectly.

Thank you, have a nice day.

[Former Member]

Hi Johan, thanks a lot for the reply.

Here's what I get with your adapted version of the code.

I think it's very close. I tried to modify it to have the good SUM at the bottom and have a row apart that doesn't take place over an existing ItemCode but it's giving me errors.

I'll try to figure this out later this morning.

Feel free to help me again it's very appreciated.

Thank you.