Sql-server – How to filter values from different table inner join and group by

sql server 2014sql-server-2016

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

Best Answer

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
Related Question