Split freight across multiple items

Question

Given an SQL Query as follows

DECLARE @StartDate NVARCHAR(MAX) = '20170815'
DECLARE @FinishDate NVARCHAR(MAX) = '20170815'      

 SELECT 
  cid.ItemCode,
  cid.InvoiceCode,
  SUM(ROUND(cid.ExtPrice, 2)) AS 'TotalExGST'
  FROM CustomerInvoice ci
    JOIN CustomerInvoiceDetail cid 
      ON ci.InvoiceCode = cid.InvoiceCode
  WHERE ci.IsVoided = 0
  AND dbo.ConvertDate_C(ci.InvoiceDate) 
    BETWEEN @StartDate AND @FinishDate
  GROUP BY ci.invoicecode, ItemCode

UNION ALL

SELECT 
'Freight',
ci.InvoiceCode,
ci.Freight
FROM CustomerInvoice ci                         
  WHERE ci.IsVoided = 0
  AND dbo.ConvertDate_C(ci.InvoiceDate) BETWEEN @StartDate AND @FinishDate
GROUP BY ci.invoicecode, ci.Freight

Which returns data like this (Only showing one invoice, for simplicity)

╔═════════════╦══════════════╦══════════════╗
║ Item Code   ║ Invoice Code ║ Total Ex GST ║
╠═════════════╬══════════════╬══════════════╣
║ Freight     ║ INV-255390   ║ 20.000000    ║
╠═════════════╬══════════════╬══════════════╣
║ ITEM-002605 ║ INV-255390   ║ 47.120000    ║
╠═════════════╬══════════════╬══════════════╣
║ ITEM-002679 ║ INV-255390   ║ 11.260000    ║
╠═════════════╬══════════════╬══════════════╣
║ ITEM-002687 ║ INV-255390   ║ 10.860000    ║
╠═════════════╬══════════════╬══════════════╣
║ ITEM-028905 ║ INV-255390   ║ 58.480000    ║
╚═════════════╩══════════════╩══════════════╝

How might I obtain the total number of line items on each invoice, and split the Freight amount evenly by that total, and then sum this to the item price?

Items and Prices are on CustomerInvoiceDetail table, whereas freight cost is on CustomerInvoice table, these can be joined on InvoiceCode column.

Expected output would look like, and be calculated like the following:

Number of items on invoice = 4
Freight cost = 20
Split Freight Cost = 20 / 4 = 5
Then add 5 to all the items, and thus remove freight row from the query.

╔═════════════╦═════════════╦════════════╗
║ ItemCode    ║ InvoiceCode ║ TotalExGST ║
╠═════════════╬═════════════╬════════════╣
║ ITEM-002605 ║ INV-255390  ║ 52.12      ║
╠═════════════╬═════════════╬════════════╣
║ ITEM-002679 ║ INV-255390  ║ 16.26      ║
╠═════════════╬═════════════╬════════════╣
║ ITEM-002687 ║ INV-255390  ║ 15.86      ║
╠═════════════╬═════════════╬════════════╣
║ ITEM-028905 ║ INV-255390  ║ 63.48      ║
╚═════════════╩═════════════╩════════════╝

Answer 1

I would wrap your queries into CTE to make it more readable.

CTE_InvoiceFreight gives one row per invoice with the Freight amount.

CTE_InvoiceItems gives invoice items.

I join them with LEFT JOIN in case there are invoices without Freight.

COUNT(*) OVER (...) gives a count of items in each invoice without extra GROUP BY.

Uncomment columns in the final SELECT to see intermediary results.

DECLARE @StartDate NVARCHAR(MAX) = '20170815'
DECLARE @FinishDate NVARCHAR(MAX) = '20170815'

WITH
CTE_InvoiceFreight
AS
(
    -- this query returns at most one row per InvoiceCode
    SELECT
        ci.InvoiceCode
        ,SUM(ci.Freight) AS TotalInvoiceFreight
    FROM
        CustomerInvoice AS ci
    WHERE
        ci.IsVoided = 0
        AND dbo.ConvertDate_C(ci.InvoiceDate) BETWEEN @StartDate AND @FinishDate
    GROUP BY
        ci.InvoiceCode
)
,CTE_InvoiceItems
AS
(
    SELECT
        cid.ItemCode
        ,cid.InvoiceCode
        ,SUM(ROUND(cid.ExtPrice, 2)) AS TotalExGST
    FROM
        CustomerInvoice AS ci
        INNER JOIN CustomerInvoiceDetail AS cid ON ci.InvoiceCode = cid.InvoiceCode
    WHERE
        ci.IsVoided = 0
        AND dbo.ConvertDate_C(ci.InvoiceDate) BETWEEN @StartDate AND @FinishDate
    GROUP BY
        cid.ItemCode
        ,cid.InvoiceCode
)
SELECT
    CTE_InvoiceItems.ItemCode
    ,CTE_InvoiceItems.InvoiceCode

--  ,CTE_InvoiceItems.TotalExGST
--  ,COUNT(*) OVER (PARTITION BY CTE_InvoiceItems.InvoiceCode) AS InvoiceItemsCount
--  ,ISNULL(CTE_InvoiceFreight.TotalInvoiceFreight, 0) AS TotalInvoiceFreight

    ,CTE_InvoiceItems.TotalExGST
    + ROUND(
        ISNULL(CTE_InvoiceFreight.TotalInvoiceFreight, 0) / 
        COUNT(*) OVER (PARTITION BY CTE_InvoiceItems.InvoiceCode)
    , 2) AS TotalWithFreightExGST
FROM
    CTE_InvoiceItems
    LEFT JOIN CTE_InvoiceFreight 
        ON CTE_InvoiceFreight.InvoiceCode = CTE_InvoiceItems.InvoiceCode
ORDER BY
    CTE_InvoiceItems.InvoiceCode
    ,CTE_InvoiceItems.ItemCode
;