how to find the difference between current column and previous column

Question

Post reply

how to find the difference between current column and previous column

Prasanthi Reddy

SSC Enthusiast

Points: 183
More actions
August 16, 2011 at 1:27 am

#390202

hi,
I have a table as below..
CREATE TABLE [dbo].[score](
[PlayerId] [int] IDENTITY(1,1) NOT NULL,
[PlayerName] [varchar](50) NULL,
[PlayerScore] [int] NULL,
[ScoreDate] [varchar](30) NULL,
[overs] [int] NULL)
and values
insert into score values('sunil',5,'16-08-2011',1)
insert into score values('sunil',8,'16-08-2011',2)
insert into score values('sunil',13,'16-08-2011',3)
insert into score values('sunil',16,'16-08-2011',4)
insert into score values('sunil',20,'16-08-2011',5)
In second over the playerscore is 1st over score + 2nd over score
like wise 3rd over score is 2nd over score + 3rd over score... goes on
Now to get only 2nd over score i need to take the difference of 2nd over score and 1st over score...
In oracle we have LEAD () and LAG () to do this.
I am new to sql server. Can any one explain how we can do it in sql server 2008 pls..
Thanks in advance..
Prasanthi Reddy.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

There isn't a perfect fit for the lead and lag in oracle but you can use the OVER clause to create a window function.

Jayanth Kurup[/url]

Prasanthi Reddy SSC Enthusiast Points: 183 More actions · Answer 2

Thanks for your reply..

Can you pls explain for the above case..

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

SELECT a.*, b.*

FROM score a

INNER JOIN score b ON b.PlayerName = a.PlayerName AND b.overs = 2

WHERE a.overs = 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Prasanthi Reddy SSC Enthusiast Points: 183 More actions · Answer 4

This gives complete score in 1st over also in 2nd over. I want the score only in 2nd over.

i.e., difference between 2nd over and 1st over's score.

Also i want to find the individual scores for all the overs. A generic query or function or procedure.

kumar20 SSC Veteran Points: 261 More actions · Answer 5

try thisSET NOCOUNT ON

DECLARE @score TABLE (

[PlayerId] [INT] IDENTITY(1,1) NOT NULL,

[PlayerName] [VARCHAR](50) NULL,

[PlayerScore] [INT] NULL,

[ScoreDate] [VARCHAR](30) NULL,

[overs] [INT] NULL)

INSERT INTO @score VALUES('sunil',5,'16-08-2011',1)

INSERT INTO @score VALUES('sunil',8,'16-08-2011',2)

INSERT INTO @score VALUES('sunil',13,'16-08-2011',3)

INSERT INTO @score VALUES('sunil',16,'16-08-2011',4)

INSERT INTO @score VALUES('sunil',20,'16-08-2011',5)

SELECT A.[PlayerId], A.[PlayerName], A.[overs], A.[PlayerScore] - ISNULL(B.[PlayerScore], 0) AS Score

FROM (SELECT *,

ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY PlayerName, Overs) AS Row

FROM @score) A

LEFT JOIN (SELECT *,

ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY PlayerName, Overs) AS Row

FROM @score) B ON B.PlayerName = A.PlayerName

AND A.Row = B.Row + 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Prasanthi Reddy (8/16/2011)
This gives complete score in 1st over also in 2nd over. I want the score only in 2nd over.
i.e., difference between 2nd over and 1st over's score.
Also i want to find the individual scores for all the overs. A generic query or function or procedure.

They are on the same row. See "subtraction" in BOL.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Prasanthi Reddy SSC Enthusiast Points: 183 More actions · Answer 7

Prasanthi Reddy

SSC Enthusiast

Points: 183

August 16, 2011 at 2:57 am

#1368979

Thank u so much... it's working fine..

Prasanthi Reddy SSC Enthusiast Points: 183 More actions · Answer 8

kumar20 (8/16/2011)
try thisSET NOCOUNT ON DECLARE @score TABLE ( [PlayerId] [INT] IDENTITY(1,1) NOT NULL, [PlayerName] [VARCHAR](50) NULL, [PlayerScore] [INT] NULL, [ScoreDate] [VARCHAR](30) NULL, [overs] [INT] NULL) INSERT INTO @score VALUES('sunil',5,'16-08-2011',1) INSERT INTO @score VALUES('sunil',8,'16-08-2011',2) INSERT INTO @score VALUES('sunil',13,'16-08-2011',3) INSERT INTO @score VALUES('sunil',16,'16-08-2011',4) INSERT INTO @score VALUES('sunil',20,'16-08-2011',5) SELECT A.[PlayerId], A.[PlayerName], A.[overs], A.[PlayerScore] - ISNULL(B.[PlayerScore], 0) AS Score FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY PlayerName, Overs) AS Row FROM @score) A LEFT JOIN (SELECT *, ROW_NUMBER() OVER (PARTITION BY PlayerName ORDER BY PlayerName, Overs) AS Row FROM @score) B ON B.PlayerName = A.PlayerName
AND A.Row = B.Row + 1

great..it's working.. If possible can you please explain PARTITION and OVER().. as I am new to sql server it would be gr8 if u explain these..

Thank you....

kumar20 SSC Veteran Points: 261 More actions · Answer 9

You can get more info about them in Books On Line(BOL) by studying window functions.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Here's another method which is 3x faster than OVER()

SELECT a.PlayerId, a.PlayerName, a.overs, a.PlayerScore - ISNULL(b.PlayerScore, 0) AS Score

FROM #score a

CROSS APPLY(SELECT [LastRow] = MAX(overs) FROM #score WHERE PlayerName = a.PlayerName AND overs < a.overs) x

LEFT JOIN #score b ON b.PlayerName = a.PlayerName AND b.overs = x.[LastRow]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden