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 2008 Forums
 Transact-SQL (2008)
 Subtracting Current Value from Previous Value		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kapital123
Starting Member

26 Posts
Posted - 01/08/2013 :  01:17:11  Show Profile  Reply with Quote
Hi all,

I have attempted all sorts of approaches to my latest dilemma to no avail. In essence what I want to do is subtract the current value from the previous available value, grouping by the company code. A sample of my data is as follows:

3 Fields + Change Column

Company_Code Date Score Change(What I Want!!)
ABC 25/05/2012 86 0
ABC 28/08/2012 86 0
ABC 01/01/2013 66 -20
XYZ 25/05/2012 89 0
XYZ 28/08/2012 95 +6
XYZ 01/01/2013 81 -14
. . .
. . .
. . .

Anyone have any ideas on the most optimal SQL Code in achieving this ?
Edited by - Kapital123 on 01/08/2013 01:20:18

visakh16
Very Important crosS Applying yaK Herder

India
48041 Posts
Posted - 01/08/2013 :  01:20:28  Show Profile  Reply with Quote 

SELECT t.*,COALESCE(t.Score - p.Score,0) AS Change
FROM table t
OUTER APPLY (SELECT TOP 1 Score
             FROM table
             WHERE Company_Code = t.Company_Code
             AND [Date] < t.[Date]
             ORDER BY [Date] DESC)p


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48041 Posts
Posted - 01/08/2013 :  01:20:58  Show Profile  Reply with Quote
Also see what all you can do with APPLY operator here

http://visakhm.blogspot.in/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Kapital123
Starting Member

26 Posts
Posted - 01/08/2013 :  04:07:12  Show Profile  Reply with Quote
Thanks Visakh16, I will give this a go
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48041 Posts
Posted - 01/08/2013 :  04:17:52  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.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.08 seconds. Powered By: Snitz Forums 2000