Calculate the each product(Inkcode) stock

问题:

I have 2 tables Tblinkreceiving and Tblinkdelivery. I want to display the Stock balance for each inkcode.I tried the below sql join query but the calculation is wrong for many inkcodes when i cross check in manual calculation.Where it went wrong ?

select r.inkcode, SUM(r.quantity) Stock-In, SUM(d.quantity) Stock-out, (SUM(r.quantity) - SUM(d.quantity)) Stock-Balance from Tblinkreceiving r,Tblinkdelivery d where r.inkcode=d.inkcode
group by r.inkcode;

回答1:

WITH    i AS
        (
        SELECT  inkcode, SUM(quantity) AS qin
        FROM    tblInkReceiving
        GROUP BY
                inkcode
        ),
        o AS
        (
        SELECT  inkcode, SUM(quantity) AS qout
        FROM    tblInkDelivery
        GROUP BY
                inkcode
        )
SELECT  COALESCE(i.inkcode, o.inkcode) AS inkcode,
        COALESCE(qin, 0) AS stock_in,
        COALESCE(qout, 0) AS stock_out,
        COALESCE(qin, 0) - COALESCE(qout, 0) AS stock_balance
FROM    i
FULL JOIN
        o
ON      o.inkcode = i.inkcode

回答2:

You have used an inner join (implicit) to combine receiving and delivery. This means that any ink code that has been received but not delivered will be excluded from your results.

If you can make the assumption that for any ink delivery there must be an ink receipt (based on the premise that you cannot deliver something that you haven't received), then you can use an inner join on tblInkReceiving, as follows:

SELECT r.inkcode, 
    SUM(r.quantity) AS Stock-In, 
    ISNULL(SUM(d.quantity), 0) AS Stock-out,
    SUM(r.quantity) - ISNULL(SUM(d.quantity), 0) AS Stock-Balance 
FROM Tblinkreceiving r LEFT JOIN Tblinkdelivery d ON r.inkcode = d.inkcode
GROUP BY r.inkcode

The left join will return all records on the left (r), including matching records on the right (d). If d has not matching records (i.e. there have been no ink deliveries for that ink code), then the values for those rows will be null.

If you cannot make the assumption that ink can only be delivered once it has been received, then you need to either link to the ink table as well (i.e. the table for which inkcode is the primary key), or union all of the ink codes in the receiving and delivery tables, and link to that:

;WITH cte AS (SELECT inkcode FROM Tblinkreceiving 
    UNION 
    SELECT inkcode FROM Tblinkdelivery)
SELECT cte.inkcode, 
    SUM(r.quantity) AS Stock-In, 
    ISNULL(SUM(d.quantity), 0) AS Stock-out,
    SUM(r.quantity) - ISNULL(SUM(d.quantity), 0) AS Stock-Balance 
FROM cte LEFT JOIN Tblinkreceiving r ON cte.ink_code = r.ink_code
    LEFT JOIN Tblinkdelivery d ON cte.inkcode = d.inkcode
GROUP BY cte.inkcode