Leonardo Trimarchi - 7 months ago 31

SQL Question

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
```