-3

I have a 2 tables,

1- Inward Table

CREATE TABLE Inward
(
    GoodsInwardId BIGINT DEFAULT NEXT VALUE FOR SEQ_GoodsInwardId NOT NULL UNIQUE,
    GoodsInwardDate DATE NOT NULL,
    InventoryId BIGINT NOT NULL,
    Quantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsInwardId)
)

2- Outward Table

CREATE TABLE GoodsOutward
(
    GoodsOutwardId BIGINT DEFAULT NEXT VALUE FOR SEQ_GoodsOutwardId NOT NULL UNIQUE,
    GoodsOutwardDate DATE NOT NULL,
    InventoryId BIGINT NOT NULL,
    Quantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsOutwardId)
)

After i filled data in both table Result such as,

enter image description here

My question is, how can i summarize query such as below screen shot.

enter image description here

2

2 Answers 2

1

Try this

SELECT Date, InventoryID, SUM(InwardQTY), SUM(OutwardQTY)
FROM (
select Date, InventoryID, SUM(QTY) as InwardQTY, NULL as OutwardQTY 
from Inward
group by Date, InventoryID
UNION ALL
select Date, InventoryID, NULL as InwardQTY, SUM(QTY) as OutwardQTY 
from Outward
group by Date, InventoryID
) A
GROUP BY Date, InventoryID
ORDER BY Date, InventoryID  
2
  • 1
    Inward Qty and outward Qty summarized data and inventory Id wise, Pls check my summary screen shot
    – M.I.M.F
    Sep 28, 2017 at 6:53
  • Have edited the answer Sep 28, 2017 at 8:13
3

You can presum the two tables and then FULL OUTER JOIN

SELECT isnull(i.Date, o.Date) AS Date, 
    isnull(i.InventoryId, o.InventoryId) AS InventoryId,
    i.Qty InwardQty ,
    o.Qty OutwardQty
FROM
(    SELECT GoodsInwardDate AS Date, InventoryId, SUM(Quantity) AS Qty
    FROM Inward
    GROUP BY Date, InventoryId
) AS i
FULL OUTER JOIN
(    SELECT GoodsOutwardDate AS Date, InventoryId, SUM(Quantity) AS Qty
    FROM GoodsOutward
    GROUP BY Date, InventoryId
) AS o 
ON i.Date=o.Date AND i.InventoryId=o.InventoryId
ORDER BY isnull(i.Date, o.Date)
4
  • getting incorrect result! First coming all the inward table records after coming all the outward table records Please check my summary screen shot in question..
    – M.I.M.F
    Sep 28, 2017 at 6:46
  • 4
    @M.I.M.F the answer seems correct to me. If you the results ordered in a specific way, add ORDER BY. That's irrelevant to the question you asked. Sep 28, 2017 at 7:03
  • @M.I.M.F, never copy and use a piece of code that you don't understand. If you figure out what the query does (seems correct to me), it is very easy to adjust it to do what you ask for. As a bonus, you will actually have learned something (which is what StackOverflow etc. is all about) Sep 28, 2017 at 9:38
  • @M.I.M.F I added the ORDER BY so you can see them in that order.
    – indiri
    Sep 28, 2017 at 14:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.