Stuck with a somewhat complex SQL query
I am currently very obsessed with my project on this SQL query which I cannot get.
Here are my tables:
[INVENTORY]
====================================================
| ITEM _ID | ITEM_DESC | STOCK | PPU |
====================================================
| 1 | CHAIR WHITE | 200 | 15.00 |
| 2 | CHAIR BLACK | 150 | 15.00 |
| 3 | CHAIR GREEN | 100 | 15.00 |
====================================================
[I_RSV]
==============================================
| ID | TRAN_CODE | ITEM_ID | QTY |
==============================================
| 1 | 1001 | 1 | 100 |
| 2 | 1001 | 2 | 50 |
| 3 | 1002 | 1 | 50 |
==============================================
[TRANSACTIONS]
=========================================================
| TRAN_CODE | TRAN_DATE | DATE_IN | DATE OUT |
=========================================================
| 1001 | 5/22/2015 | 5/26/2015 | 5/27/2015 |
| 1002 | 5/22/2015 | 5/30/2015 | 5/31/2015 |
=========================================================
So the aim is to request ALL ITEMS DETAILS and the TOTAL OF ALL RESERVED GOODS THAT HAVE NOT PASSED TODAY. This will be the target as a table:
[TABLE X]
=============================================================
| ITEM_ID | ITEM_DESC | PPU | STOCK | RESERVED |
=============================================================
| 1 | CHAIR WHITE | 15.00 | 200 | 150 |
| 2 | CHAIR BLACK | 15.00 | 150 | 50 |
| 3 | CHAIR GREEN | 15.00 | 100 | 0 |
=============================================================
So far, this is what I have done:
SELECT ITEM_ID, ITEM_DESC, PPU, STOCK, (SELECT SUM(QTY) FROM I_RSV WHERE
DATE_OUT < GETDATE() GROUP BY ITEM_ID) FROM INVENTORY
But I cannot get it to work. Any help would be so much appreciated.
source to share
Here's what I would do:
- Separate the two queries.
- Put your selection logic in an inline query and summarize the results.
- Join the two on the left so you can still see the products that don't have matching reservations.
You can do this in a single bit query, but I think you will find it works better for readability and ease of building queries.
Create table #INVENTORY (Item_ID int,Item_Desc varchar(50),Stock Int,PPU real);
Insert Into #INVENTORY values
(1,'CHAIR WHITE',200,15.00)
,(2,'CHAIR BLACK',150,15.00)
,(3,'CHAIR GREEN',100,15.00)
Create Table #I_RSV (ID Int,Tran_Code int,Item_ID int,Qty Int)
Insert into #I_RSV values
(1,1001,1,100),(2,1001,2,50),(3,1002,1,50)
Create Table #TRANSACTIONS
(TRAN_CODE int,TRAN_DATE date, DATE_IN date, [DATE OUT] date)
Insert into #TRANSACTIONS Values
(1001,'5/20/2015','5/20/2015','5/20/2015')
,(1002,'5/22/2015','5/30/2015','5/31/2015')
Select I.*,Case when R.Qty is null then 0 else R.Qty end as Reserved
from #INVENTORY as I
Left join
( select SUM(Qty) as Qty ,R.Item_ID
from #I_RSV as R inner join #TRANSACTIONS as T
on R.Tran_Code=T.TRAN_CODE
Where T.[DATE OUT] < GETDATE()
group by R.Item_ID) as R
on R.Item_ID=I.Item_ID;
drop table #INVENTORY;
drop table #TRANSACTIONS;
drop table #I_RSV;
source to share
You need to concatenate three tables together for the query to function
SELECT INV.ITEM_ID, INV.ITEM_DESC, INV.PPU, INV.STOCK, SUM(I_RSV.QTY)
FROM INVENTORY INV
JOIN I_RSV ON I_RSV.ITEM_ID = INVENTORY.ITEM_ID
JOIN TRANSACTIONS TRN ON I_RSV.TRAN_CODE = TRN.TRAN_CODE
WHERE TRN.TRAN_CODE.DATE_OUT < GETDATE()
GROUP BY INV.ITEM_ID
source to share
Addition to @ Rigel1121's answer
SELECT
ITEM_ID,
ITEM_DESC,
PPU,
STOCK,
SUM(QTY),
STOCK - SUM(QTY) 'FREE TO RESERVE' --free from reservation qty
FROM INVENTORY A
INNER JOIN I_RSV B ON A.ITEM_ID=B.ITEM_ID
INNER JOIN TRANSACTIONS C ON B.TRAN_CODE=C.TRAN_CODE
WHERE DATE_OUT < GETDATE()
GROUP BY ITEM_ID,ITEM_DESC,PPU,STOCK,[DATE OUT]
source to share
Try the following:
SELECT INVENTORY.ITEM_ID, INVENTORY.ITEM_DESC, INVENTORY.STOCK, INVENTORY.PPU, SUM(I_RSV.QTY)
FROM INVENTORY
LEFT JOIN I_RSV
ON INVENTORY.ITEM_ID = I_RSV.ITEM_ID
LEFT JOIN TRANSACTION
ON TRANSACTION.TRAN_CODE = I_RSV.TRAN_CODE
WHERE TRANSACTION.DATE_OUT > GETDATE()
GROUP BY INVENTORY.ITEM_ID, INVENTORY.ITEM_DESC, INVENTORY.STOCK, INVENTORY.PPU
yours DATE_OUT < GETDATE()
is wrong. where you don't want to go today, you should date_out> getdate()
. or yours should specify it as GETDATE() < DATE_OUT
getdate = today <dates
source to share