SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SQL Running Substraction
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asyssolvers
Starting Member

9 Posts

Posted - 04/30/2011 :  05:28:40  Show Profile  Reply with Quote
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

USA
11 Posts

Posted - 05/15/2011 :  00:39:22  Show Profile  Reply with Quote
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
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 05/17/2011 :  04:33:01  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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/

Edited by - mmarovic on 05/17/2011 04:35:06
Go to Top of Page

Andrew Zwicker
Starting Member

USA
11 Posts

Posted - 05/17/2011 :  22:26:11  Show Profile  Reply with Quote
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
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 05/18/2011 :  04:26:11  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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: http://mirko-marovic-eng.blogspot.com/2011/02/running-totals-in-sql-back-to-future.html

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000