I have sales line table that each row represent row in invoice sum price of the row etc. I also have sales headers table that each row represent invoice + discount (the rows represented in the sales line table).
I need to calculate for each item the quantity sales and the sum price of the item sales in each row after discount. I wonder if I calculate the price after discount correctly or if I missed something.

Here is the query

SELECT 
    SL.ItemCode, 
    SUM(SL.Qty) AS TotalQuantity,  

    SUM(SL.LineSum) - SUM(ISNULL(SH.DocDiscount, 0) * (SL.LineSum / ISNULL(TotalInvoiceSum.TotalSum, 0))) AS TotalSalesAfterDiscount ,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount  
FROM SalesLine SL
JOIN SalesHeader SH ON SL.DocNum = SH.DocNum  
JOIN (
    -- Calculate total sum per invoice
    SELECT DocNum, SUM(LineSum) AS TotalSum
    FROM SalesLine
    GROUP BY DocNum
) AS TotalInvoiceSum ON SL.DocNum = TotalInvoiceSum.DocNum
GROUP BY SL.ItemCode;

sales line table sample

I have sales line table that each row represent row in invoice sum price of the row etc. I also have sales headers table that each row represent invoice + discount (the rows represented in the sales line table).
I need to calculate for each item the quantity sales and the sum price of the item sales in each row after discount. I wonder if I calculate the price after discount correctly or if I missed something.

Here is the query

SELECT 
    SL.ItemCode, 
    SUM(SL.Qty) AS TotalQuantity,  

    SUM(SL.LineSum) - SUM(ISNULL(SH.DocDiscount, 0) * (SL.LineSum / ISNULL(TotalInvoiceSum.TotalSum, 0))) AS TotalSalesAfterDiscount ,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount  
FROM SalesLine SL
JOIN SalesHeader SH ON SL.DocNum = SH.DocNum  
JOIN (
    -- Calculate total sum per invoice
    SELECT DocNum, SUM(LineSum) AS TotalSum
    FROM SalesLine
    GROUP BY DocNum
) AS TotalInvoiceSum ON SL.DocNum = TotalInvoiceSum.DocNum
GROUP BY SL.ItemCode;

sales line table sample

sales headers table sample

• sql
• sql-server

• Sample data and expected results would really help. Does DocDiscount represent a percentage or a total discount for the whole SalesHeader? How does DocNum coincide with SalesLine and SalesHeader rows? – Charlieface Commented Mar 17 at 16:10
• I suspect that Header.Discount is the total discount for all rows to be distributed evently per product, but who knows, the question description is so vague it makes david lynch movies straight forward – siggemannen Commented Mar 17 at 16:51
• @Charlieface docDiscount represent totalDiscount on 1 invoice (not precentage) i have added sample data to the quastion – Shlomo Halev Commented Mar 17 at 17:12
• Please show sample data and expected results as text not images. – Charlieface Commented Mar 17 at 17:20
• Please do not upload images of code/data/errors when asking a question. – Thom A Commented Mar 17 at 17:31

2 Answers 2

You can use a window function for this. Just window it per DocNum and sum up the LineSum, then you can apportion the DocDiscount based on that.

SELECT 
  SL.ItemCode,
  COUNT(DISTINCT SL.DocNum) AS InvoiceCount,
  SUM(SL.Qty) AS TotalQuantity,
  SUM(SL.FinalLineSum) AS TotalSalesAfterDiscount
FROM (
    SELECT
      SL.*,
      SL.LineSum - ISNULL(
        SH.DocDiscount, 0) * SL.LineSum
        / NULLIF(SUM(SL.LineSum) OVER (PARTITION BY SL.DocNum), 0)  -- NULLIF in case of division by zero
      ) AS FinalLineSum
    FROM SalesLine SL
    JOIN SalesHeader SH ON SH.DocNum = SL.DocNum
) AS SL
GROUP BY
  SL.ItemCode;

Correctness

With a small dataset and hand-verification, you can verify that your query is in fact right, you did not miss anything.

With a dataset of 2 invoices of 2 products each (codes 123 and 999, at an initial price of 20 whatever-you-want each),
when each product gets ordered 4 times (1 123 + 3 999 on the 25 %-discounted invoice; and 3 123 + 1 999 on the (not discounted) second invoice):

You obtain:

which corresponds to the sums of individual lines (15 + 60 for 123, 45 + 20 for 999).

This fitting a fiddle.

Efficiency

Additionally, you can avoid joining twice to SalesLine by using a window function that will gives each SalesLine a copy of the sum(LineSum) of all its siblings (as if you computed TotalInvoiceSum grouped by DocNum but, instead of materializing it as a separate subtable, you directly associated each SalesLine with a copy of the result corresponding to its DocNum).

Thus the following query returns the same as yours:

WITH SL AS (SELECT *, SUM(LineSum) OVER (PARTITION BY DocNum) TotalSum FROM SalesLine SL)
SELECT
    SL.ItemCode,
    SUM(SL.Qty) AS TotalQuantity,
    SUM(SL.LineSum - COALESCE(SH.DocDiscount * SL.LineSum / TotalSum, 0)) AS TotalSalesAfterDiscount,
    COUNT(DISTINCT SL.DocNum) AS InvoiceCount  
FROM SL
JOIN SalesHeader SH ON SL.DocNum = SH.DocNum  
GROUP BY SL.ItemCode;

(note I used a Common Table Expression, introduced by the WITH, instead of a subselect, to show SL as an independant first step)