asked on

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:

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.

Jared_S

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

ASKER CERTIFIED SOLUTION

Jared_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

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.

trims30

ASKER

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