SQL Running Substraction - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Running Substraction

Author

Topic

asyssolvers
Starting Member

9 Posts

Posted - 2011-04-30 : 05:28:40

I have a result set as below.

Item ExpectedQty ReceivedQty Short
Item01 30 45 5
Item01 20 45 5
Item02 40 38 2
item03 50 90 10
item03 30 90 10
item03 20 90 10

query is:

select a.Item, a.ExpectedQty,b.ReceivedQty, b.Short
from a join b on a.Item = b.Item

I need to get result as in second chart. Basically I have a total of received quantity in each line and I need to show received quantity against Expected Quantity, if there is any shortage I need to show in last line.

Expected:

Item ExpectedQty ReceivedQty Short
item01 30 30 0
item01 20 15 5
item02 40 38 2
item03 50 50 0
item03 30 30 0
item03 20 10 10

Thanks in advance.

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-15 : 00:39:22

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

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-17 : 04:33:01

The best solution is to return the client:

select a.Item, a.ExpectedQty,b.ReceivedQty, b.Short
from a join b on a.Item = b.Item
order by a.item, a.orderDate

Then calculate running values on the client.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-17 : 22:26:11

Hi,

In a way I agree it is more appropriate to have this type of processing be done by the client [in the business logic level, in this case]. However, the decision of where to put logic isn't always clear-cut.

For example, what if the source code isn't available or can only be modified by a 3rd party vendor? What if some other SQL task needs the information that is outputted by this query?

I've posted some thoughts on the subject in my helpwithsql blog. I welcome comments/feedback.

Thanks.

Visit http://www.helpwithsql.com

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-05-18 : 04:26:11

Well, if there is no other choice, you can choose between slow, resource intensive and not quite reliable queries. This problem is similar to running totals problem. You can find a couple of articles and long, interesting discussion here: [url]http://mirko-marovic-eng.blogspot.com/2011/02/running-totals-in-sql-back-to-future.html[/url]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources