Stuck with a somewhat complex SQL query

Question

I am currently quite stuck with my project on this SQL query that I can't get to work.

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 goal is to query ALL ITEM DETAILS and the SUM OF ALL RESERVED ITEMS THAT HASN'T PASSED TODAY'S DATE. This would be the goal in table form:

[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 had 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 can't seem to get it work. Any help would be appreciated so much.

Answer 1

Here is what I would do:

1. Separate the two queries.
2. Put your Selection logic into the inline query and sum the results.
3. Left join the two so that you can still see products that don't have any matching reservations.

You can do this in one bit query but I think you will find this works better for readability and ease of query construction.

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;