Leonardo Trimarchi Leonardo Trimarchi - 2 months ago 9
SQL Question

Create One Result set from 3 different Queries

I am trying to create a result set using three queries. I have three tables an inventory table, a form order table, and a form order detail table. I need to be able to input a date range and get how many forms were ordered, how many are in current inventory, and how many were destroyed based on if they contain a destruction date. Ultimately i want a result set that shows:

InventoryId, FormDescription, Product, Ordered, Shipped, Destroyed, Total ending


What would be the best way to get that result set using these queries?

These are my three Queries

SELECT FOD.InventoryId, SUM(FOD.FormOrderAmount) as totalOrdered, FOD.FormShippedAmount FROM tblFormOrder FMO
JOIN tblFormOrderDetails FOD ON FOD.FormOrderId = FMO.FormOrderId
WHERE FMO.OrderDateTime BETWEEN '20110101' and '20120101'
AND FMO.OrderStatus IN ('S')
GROUP BY FOD.InventoryId, FOD.FormShippedAmount -- total shipped by date and inventoryid



SELECT INV.InventoryId, SUM(INV.CurrentAmount) as currentAmount, SUM(INV.OrderAmount) as OrderAmount,
(SUM(INV.OrderAmount) - SUM(INV.CurrentAmount)) as InventoryUsed
FROM tblInventory INV
where INV.CreatedOn
BETWEEN '20110101' and '20120101'
GROUP BY INV.InventoryId -- current amount based off ordered and used



select INV.InventoryId, count(*) as total
, FMO.OrderDateTime as OrderDate, Inv.FormNo, INV.FormDescription, INV.Product
from [tblinventory] INV
join tblformorderdetails FOD ON FOD.InventoryId = inv.InventoryId
join tblformorder FMO on FMO.FormOrderId = FOD.FormOrderId
where INV.DestructionDate
BETWEEN '20110101' and '20120101'
group by
FMO.OrderDateTime,
Inv.FormNo, INV.FormDescription, INV.Product, INV.InventoryId -- using count to find how many destroyed if they have a destruction date

Answer

Make each query a subquery that you join together.

SELECT *
FROM (
    SELECT FOD.InventoryId, SUM(FOD.FormOrderAmount) as totalOrdered,  FOD.FormShippedAmount  FROM tblFormOrder FMO 
    JOIN tblFormOrderDetails FOD ON FOD.FormOrderId = FMO.FormOrderId 
    WHERE FMO.OrderDateTime BETWEEN '20110101' and '20120101' 
    AND FMO.OrderStatus IN ('S')
    GROUP BY FOD.InventoryId, FOD.FormShippedAmount -- total shipped by date and inventoryid
) AS q1
LEFT JOIN (
    SELECT INV.InventoryId, SUM(INV.CurrentAmount) as currentAmount, SUM(INV.OrderAmount) as OrderAmount, 
    (SUM(INV.OrderAmount) - SUM(INV.CurrentAmount)) as InventoryUsed
     FROM tblInventory INV
    where INV.CreatedOn 
    BETWEEN '20110101' and '20120101' 
    GROUP BY INV.InventoryId -- current amount based off ordered and used
 ) AS q2 ON q1.InventoryId = q2.InventoryId
LEFT JOIN (
    select INV.InventoryId, count(*) as total
    ,  FMO.OrderDateTime as OrderDate, Inv.FormNo, INV.FormDescription, INV.Product 
     from [tblinventory] INV
    join tblformorderdetails FOD ON FOD.InventoryId = inv.InventoryId 
    join tblformorder FMO on FMO.FormOrderId = FOD.FormOrderId
    where INV.DestructionDate  
    BETWEEN '20110101' and '20120101' 
    group by  
    FMO.OrderDateTime,
     Inv.FormNo, INV.FormDescription, INV.Product, INV.InventoryId -- using count to find how many destroyed if they have a destruction date
) AS q3 ON q1.InventoryId = q3.InventoryId