0

There Are 5 Tables BASE_Inventory, BASE_GoodsInward, BASE_GoodsInwardLine, BASE_GoodsOutward, BASE_GoodsOutwardLine

  • BASE_Inventory Table Inner Join to BASE_GoodsInwardLine and BASE_GoodsOutwardLine
  • BASE_GoodsInward Inner Join to BASE_GoodsInwardLine Table
  • BASE_GoodsOutward Inner Join to BASE_GoodsOutwardLine Table

Tables are below,

Inventory Table

CREATE TABLE BASE_Inventory
(
InventoryId BIGINT NOT NULL UNIQUE,
ItemNameCode VARCHAR(45) NOT NULL,
ItemDescription1 VARCHAR(255) NOT NULL,
CurrentQuantity DECIMAL(18, 4) DEFAULT 0.00 NOT NULL
PRIMARY KEY(InventoryId)
)

Goods Inward Table

CREATE TABLE BASE_GoodsInward
(
    GoodsInwardId BIGINT NOT NULL UNIQUE,
    GoodsInwardDate DATE NOT NULL
    PRIMARY KEY(GoodsInwardId)
)

CREATE TABLE BASE_GoodsInwardLine
(
    GoodsInwardLineId BIGINT NOT NULL UNIQUE,
    GoodsInwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    InwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsInwardLineId)
)

Goods Outward Table

CREATE TABLE BASE_GoodsOutward
(
    GoodsOutwardId BIGINT NOT NULL UNIQUE,
    GoodsOutwardDate DATE NOT NULL
    PRIMARY KEY(GoodsOutwardId)
)

CREATE TABLE BASE_GoodsOutwardLine
(
    GoodsOutwardLineId BIGINT NOT NULL UNIQUE,
    GoodsOutwardId BIGINT NOT NULL,
    InventoryId BIGINT NOT NULL,
    OutwardQuantity DECIMAL(18, 4) NOT NULL
    PRIMARY KEY(GoodsOutwardLineId)
)

Data with table

Inventory

enter image description here

Inward

enter image description here

Outward

enter image description here

I want to retrieve 2 type of result query from above result Inward & Outward tables (Inward and Outward Quantity should be SUM of Inventory ID)

1: Date wise Inventory Inward & Outward

enter image description here

2: Inventory wis Inward & Outward

enter image description here

1 Answer 1

0

Probably someone can more optimally write this but it is fast:

  --1.
    SELECT alll.Date, alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory 
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId  
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL 
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory 
    INNER JOIN BASE_GoodsOutwardLine ON BASE_Inventory.InventoryId = BASE_GoodsOutwardLine.InventoryId  
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId 
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode
    UNION ALL 
    SELECT BASE_GoodsInward.GoodsInwardDate AS "Date", BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine 
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId  
    GROUP BY BASE_GoodsInward.GoodsInwardDate, BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode 
    UNION ALL 
    SELECT BASE_GoodsOutward.GoodsOutwardDate AS "Date", BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity 
    FROM BASE_GoodsOutwardLine 
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId  
    GROUP BY BASE_GoodsOutward.GoodsOutwardDate, BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode
    ) alll 
    GROUP BY alll.Date, alll.InventoryId, alll.ItemNameCode 
    ORDER BY alll.Date, alll.InventoryId, alll.ItemNameCode;


    --2.
    SELECT alll.InventoryId, alll.ItemNameCode, SUM(alll.CurrentQuantity) AS CurrentQuantity, SUM(alll.InwardQuantity) AS InwardQuantity, SUM(alll.OutwardQuantity) AS OutwardQuantity FROM (
    SELECT BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode, SUM(BASE_Inventory.CurrentQuantity) AS CurrentQuantity, 0 AS InwardQuantity, 0 AS OutwardQuantity  FROM BASE_Inventory 
    INNER JOIN BASE_GoodsInwardLine ON BASE_Inventory.InventoryId = BASE_GoodsInwardLine.InventoryId  
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    GROUP BY BASE_Inventory.InventoryId, BASE_Inventory.ItemNameCode 
    UNION ALL
    SELECT BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, SUM(BASE_GoodsInwardLine.InwardQuantity) AS InwardQuantity, 0 AS OutwardQuantity
    FROM BASE_GoodsInwardLine 
    INNER JOIN BASE_GoodsInward ON BASE_GoodsInwardLine.GoodsInwardId = BASE_GoodsInward.GoodsInwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsInwardLine.InventoryId = BASE_Inventory.InventoryId  
    GROUP BY BASE_GoodsInwardLine.InventoryId, BASE_Inventory.ItemNameCode 
    UNION ALL 
    SELECT BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode, 0 AS CurrentQuantity, 0 AS InwardQuantity, SUM(BASE_GoodsOutwardLine.OutwardQuantity) AS OutwardQuantity 
    FROM BASE_GoodsOutwardLine 
    INNER JOIN BASE_GoodsOutward ON BASE_GoodsOutwardLine.GoodsOutwardId = BASE_GoodsOutward.GoodsOutwardId
    INNER JOIN BASE_Inventory ON BASE_GoodsOutwardLine.InventoryId = BASE_Inventory.InventoryId  
    GROUP BY BASE_GoodsOutwardLine.InventoryId, BASE_Inventory.ItemNameCode 
    ) alll 
    GROUP BY alll.InventoryId, alll.ItemNameCode 
1
  • deliberately I did not use aliases
    – MartinusP
    Commented Jul 23, 2017 at 2:59

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.