• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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.
0
trims30
Asked:
trims30
  • 2
1 Solution
 
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
 
Jared_SCommented:
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
 
QlemoC++ DeveloperCommented:
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now