Hi,
Here is a SQL statement that should work, although I made the following assumption: That the Expected Quantity table has some sort of ordering for the records. In the solution, I've used a datetime to order records, although the logic should only require minor tweaking if the records are ordered by an identity field/integer value instead.
First, I ran the following to recreate the situation [using the dt column to order records]:
create table Expected(dt datetime, Item varchar(10), ExpectedQty int)
create table Received(Item varchar(10), ReceivedQty int, Short int)
insert into Expected values('1/1/2011','Item01','30')
insert into Expected values('1/2/2011','Item01','20')
insert into Expected values('1/1/2011','Item02','40')
insert into Expected values('1/1/2011','Item03','50')
insert into Expected values('1/2/2011','Item03','30')
insert into Expected values('1/3/2011','Item03','20')
insert into Received values('Item01','45', '5')
insert into Received values('Item02','38', '2')
insert into Received values('Item03','90', '10')
The following query provides the results from the first result set below [the incorrect one]:
select ex.Item, ex.ExpectedQty,rec.ReceivedQty, rec.Short
from Expected ex inner join Received rec on ex.Item = rec.Item
The following sql has the corrected version of the query:
select
ex.item,
ex.ExpectedQty,
ex.ExpectedQty - case
when Short > threshholdForShort and Short-threshholdForShort <= ExpectedQty then Short-threshholdForShort
when Short > threshholdForShort and Short-threshholdForShort > ExpectedQty then ExpectedQty
else 0 end
as 'ReceivedQty',
case
when Short > threshholdForShort and Short-threshholdForShort <= ExpectedQty then Short-threshholdForShort
when Short > threshholdForShort and Short-threshholdForShort > ExpectedQty then ExpectedQty
else 0 end
as 'TotalShort'
from
(
-- This query gives an indication of how significant the 'Short' value needs to be
-- before it impacts this particular record.
select e1.dt, e1.item, isnull(sum(e2.ExpectedQty), 0) as 'threshholdForShort'
From Expected e1
left join Expected e2 on e1.item = e2.item and e1.dt < e2.dt
group by e1.dt, e1.item
) minShortVal
inner join Expected ex on ex.item = minShortVal.item and ex.dt = minShortVal.dt
inner join Received rec on minShortVal.item = rec.item
I tested this with the modified scenario where I added the following record:
insert into Expected values('1/4/2011','Item03','30')
In the scenario, I also set the Short value for Item3 to 40 instead of 10. The logic correctly shows 10 received and 10 short for the 3rd Item3 record and 30 short for the 4th Item3 record.
I hope this helps.
- Andrew Zwicker
Visit http://www.helpwithsql.com
SQL Running Substraction
Posted - 04/30/2011 : 05:28:40
