Need SQL Statement Help

Have to print report showing inventory on hand as of a specific date.
I know Qty On Hand as of right now and also transactions for Quantities  Received and used by date.

Want a total of On Hand plus Qty Used since specified date less Qty Received since specified date for each item.
See below:
Data table Sample
Here's the resulting data I want from the SQL Query for 8/31/2012:

A-100  Hydraulic Cylinder  6
A-101  Cam Shaft  6

Any help is appreciated.
trims30Asked:
Who is Participating?
 
Jared_SConnect With a Mentor Commented:
Or you can write the same thing like this if it's easier to follow:

declare @Date as datetime

set @Date = '08/31/2012'

select I.StockNo, 
isnull(I.OnHand,0) - 
(select sum(qty) as [used] from Inventory_Transaction_Table U where U.Inv_ID = I.Inv_ID and Action = 'USED' and Date <= @Date group by INV_ID),0) +
isnull((select sum(qty) as [rec] from Inventory_Transaction_Table R where R.Inv_ID = I.Inv_ID and Action = 'RECEIVED' and Date <= @Date group by INV_ID),0)
as [OnHand]
from Inventory_Table I

Open in new window

0
 
Jared_SCommented:
See if this works for you...

ddeclare @Date as datetime

set @Date = '08/31/2012'

select I.StockNo, 
isnull(I.OnHand,0) - isnull(U.used,0) + isnull(R.rec,0) as [OnHand]
from Inventory_Table I
inner join
(select INV_ID, sum(qty) as [used] from Inventory_Transaction_Table U where  Action = 'USED' and Date <= @Date group by INV_ID) U 
on i.inv_id = u.inv_id
inner join 
(select INV_ID, sum(qty) as [rec] from Inventory_Transaction_Table R where Action = 'RECEIVED' and Date <= @Date group by INV_ID) R
on I.Inv_ID = R.Inv_ID 

Open in new window

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
In the second code snippet the GROUP BYs are superfluous, but do no harm.
However, using a subselect in SELECT column clause should be avoided, as it often performs worse.
The first approach could also be written as:
declare @Date as datetime
set @Date = '08/31/2012'

select I.StockNo, 
isnull(I.OnHand,0) - isnull(used,0) + isnull(rec,0) as [OnHand]
from Inventory_Table I
join
(select INV_ID, 
  sum(case Action when 'USED' then qty else 0 end) as [used],
  sum(case Action when 'RECEIVED' then qty else 0 end) as [rec]
 from Inventory_Transaction_Table U
 where action in ('USED', 'RECEIVED')
   and Date <= @Date
 group by INV_ID) U 
on i.inv_id = u.inv_id

Open in new window

which I like slightly more.
0
 
trims30Author Commented:
Jared

Except for a couple of typos - the code works great!.
Was missing an Isnull(,  + - was reversed and Date selection should have been >=

Many thanks.

Here's corrected code that works for me.

declare @Date as datetime

set @Date = '08/31/2012'

select I.StockNo, 
isnull(I.OnHand,0) +
isnull((select sum(qty) as [used] from Inventory_Transaction_Table U where U.Inv_ID = I.Inv_ID and Action = 'USED' and Date >= @Date group by INV_ID),0)  -
isnull((select sum(qty) as [rec] from Inventory_Transaction_Table R where R.Inv_ID = I.Inv_ID and Action = 'RECEIVED' and Date >= @Date group by INV_ID),0)
as [OnHand]
from Inventory_Table I

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.