Difrrence between two rows

Question

Difrrence between two rows

govind_mcs

Newbie

Points: 1
More actions
April 11, 2009 at 4:43 am

#133038

Difference between Next row and previous row in sql server query
Name Reading Result
UNDRI1010-10=0
UNDRI1111-10=1
UNDRI2020-11=9
UNDRI2020-20=0
UNDRI2020-20=0
UNDRI2222-20=2
UNDRI4545-22=23
UNDRI2020-45=-25
i want this type of results........
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Kavin-1064454 SSC Veteran Points: 260 More actions · Answer 1

Just try this code. I have tried in a very straight way. I dont know is there is any tricky things are there to do. If you know also please let me know.

--Name Reading Result

--UNDRI1010-10=0

--UNDRI1111-10=1

--UNDRI2020-11=9

--UNDRI2020-20=0

--UNDRI2222-20=2

--UNDRI4545-22=23

--UNDRI2020-45=-25

--drop table #tmp

create table #tmp

(

namevarchar(10),

readingint,

resultint,

sq_noint identity(1,1),

flagchar

)

insert into #tmp

(name , reading)

select'UNDRI',10

insert into #tmp

(name , reading)

select'UNDRI',11

insert into #tmp

(name , reading)

select'UNDRI',20

insert into #tmp

(name , reading)

select'UNDRI',20

insert into #tmp

(name , reading)

select'UNDRI',20

insert into #tmp

(name , reading)

select'UNDRI',22

insert into #tmp

(name , reading)

select'UNDRI',45

insert into #tmp

(name , reading)

select'UNDRI',20

declare@noint,

@first_rowint,

@second_rowint

while (selectcount('x')

from#tmp

whereflag is null) > 0

begin

select@no =max(sq_no)

from#tmp

whereflag is null

select@first_row=reading

from#tmp

whereflag is null

andsq_no=@no

select@second_row=reading

from#tmp

whereflag is null

andsq_no=@no - 1

update#tmp

setresult=@first_row - @second_row

whereflag is null

andsq_no=@no

update#tmp

setflag='Y'

whereflag is null

andsq_no=@no

end

select * from #tmp

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 2

Hi

In addition to the above solution provided by Kavin, You can even do it with Excel if the volume is not high. Doing this in Excel is pretty straight forward.

1) Export the rows to Excel

2) Put a Forumala i.e. =CurrentRow_Cell_Number - PreviousRow_Cell_Number

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Simple solution in SQL...

Create Table VJ(

name varchar(10),

reading int,

sq_no int identity(1,1))

insert into VJ

(name , reading)

select 'UNDRI',10

insert into VJ

(name , reading)

select 'UNDRI',11

insert into VJ

(name , reading)

select 'UNDRI',20

insert into VJ

(name , reading)

select 'UNDRI',20

insert into VJ

(name , reading)

select 'UNDRI',20

insert into VJ

(name , reading)

select 'UNDRI',22

insert into VJ

(name , reading)

select 'UNDRI',45

insert into VJ

(name , reading)

select 'UNDRI',20

CREATE VIEW v_vj as

SELECT o.*,(SELECT MAX(sq_no) FROM VJ i WHERE I.SQ_NO < o.sq_no) as PRE_sq_no FROM VJ O

SELECT o.name,o.reading,(select o.reading-i.reading from VJ i where i.sq_no = o.pre_sq_no) AS RESULT

FROM V_VJ o

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

Hi

And a completely set based solution:

DECLARE @t TABLE (Name VARCHAR(30), Reading INT, Result INT)

INSERT INTO @t

SELECT 'UNDRI', '10', NULL

UNION ALL SELECT 'UNDRI', '11', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

UNION ALL SELECT 'UNDRI', '22', NULL

UNION ALL SELECT 'UNDRI', '45', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

; WITH

myCTE (RowNum, Name, Reading, Result) AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY Name, Reading),

Name,

Reading,

Result

FROM @t

)

UPDATE t1 SET t1.Result = t1.Reading - ISNULL(t2.Reading, 0)

FROM myCTE t1

JOIN myCTE t2 ON t1.Name = t2.Name AND t1.RowNum - 1 = t2.RowNum

-- Finally update the first row which was excluded previously

UPDATE @t SET Result = 0 WHERE Result IS NULL

SELECT * FROM @t

@kevin

If possible try to avoid any cursors or loops ;-). Maybe search this site for "RBAR", there are some amazing articles by Jeff Moden and other people.

Greets

Flo

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 5

Its really cool...with 3 different solutions..and all of them are independet :):-)

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Kavin-1064454 SSC Veteran Points: 260 More actions · Answer 6

Yes I can also think the different ways of approaching a problem. As I am new to SQL I hope this website will teach me a lot and correct my mistakes.

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 7

Kavin (4/11/2009)
Yes I can also think the different ways of approaching a problem. As I am new to SQL I hope this website will teach me a lot and correct my mistakes.

Yup..Learning a lot..i am also new to SQL Serve 🙂 Kudos to the team who is maintiang this clean and good forum.

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Kavin (4/11/2009)
Yes I can also think the different ways of approaching a problem. As I am new to SQL I hope this website will teach me a lot and correct my mistakes.

I also learn everyday new things here. 🙂

Greets

Flo

mister.magoo SSC-Forever Points: 47068 More actions · Answer 9

Borrowing a bit from Flo... another set based solution without CTE ....

DECLARE @t TABLE (Name VARCHAR(30), Reading INT, Result INT)

INSERT INTO @t

SELECT 'UNDRI', '10', NULL

UNION ALL SELECT 'UNDRI', '11', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

UNION ALL SELECT 'UNDRI', '22', NULL

UNION ALL SELECT 'UNDRI', '45', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

SELECT

[Name],

[Reading],

[Reading]-ISNULL(

(SELECT [Reading] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [RowNum],[Reading] FROM @t) b WHERE b.RowNum=a.RowNum-1)

,0) as Variance

FROM

(

SELECTROW_NUMBER() OVER (ORDER BY [Name]) AS RowNum,

[Name],

[Reading]

FROM @t

) a

This is an output solution, it does not affect the table.

Also, it is kind of unreliable without a row id to start with....

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 10

mister.magoo (4/11/2009)
Borrowing a bit from Flo...

Always welcome! That's the reason for sample data! 🙂

DECLARE @t TABLE (Name VARCHAR(30), Reading INT, Result INT)
INSERT INTO @t
SELECT 'UNDRI', '10', NULL
UNION ALL SELECT 'UNDRI', '11', NULL
UNION ALL SELECT 'UNDRI', '20', NULL
UNION ALL SELECT 'UNDRI', '20', NULL
UNION ALL SELECT 'UNDRI', '20', NULL
UNION ALL SELECT 'UNDRI', '22', NULL
UNION ALL SELECT 'UNDRI', '45', NULL
UNION ALL SELECT 'UNDRI', '20', NULL
SELECT
[Name],
[Reading],
[Reading]-ISNULL(
(SELECT [Reading] FROM (SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [RowNum],[Reading] FROM @t) b WHERE b.RowNum=a.RowNum-1)
,0) as Variance
FROM
(
SELECTROW_NUMBER() OVER (ORDER BY [Name]) AS RowNum,
[Name],
[Reading]
FROM @t
) a
This is an output solution, it does not affect the table.
Also, it is kind of unreliable without a row id to start with....

Nice approach. Just be careful with sub-queries in SELECT-clause. The sometimes also create an internal loop.

Greets

Flo

mister.magoo SSC-Forever Points: 47068 More actions · Answer 11

Florian Reischl (4/11/2009)
mister.magoo (4/11/2009)
Borrowing a bit from Flo...
Always welcome! That's the reason for sample data! 🙂
This is an output solution, it does not affect the table.
Also, it is kind of unreliable without a row id to start with....
Nice approach. Just be careful with sub-queries in SELECT-clause. The sometimes also create an internal loop.
Greets
Flo

We could do without the sub-query by using an outer join...

SELECT

a.[Name],

a.[Reading],

a.[Reading]-ISNULL(b.[Reading],0) as Variance

FROM

(

SELECTROW_NUMBER() OVER (ORDER BY [Name]) AS RowNum,

[Name],

[Reading]

FROM @t

) a

LEFT OUTER JOIN

(

SELECTROW_NUMBER() OVER (ORDER BY [Name]) AS RowNum,

[Name],

[Reading]

FROM @t

) b

ON b.RowNum=a.RowNum-1

And if the source table had RowNum already in it....we wouldn't need all that mess with ROW_NUMBER...

SELECT

a.[Name],

a.[Reading],

a.[Reading]-ISNULL(b.[Reading],0) as Variance

FROM

SourceData a

LEFT OUTER JOIN

SourceData a

ON b.RowNum=a.RowNum-1

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 12

Hi mister.magoo

Nice solution! So let's add the CROSS APPLY solution for the completion 🙂

DECLARE @t TABLE (Name VARCHAR(30), Reading INT, Result INT)

INSERT INTO @t

SELECT 'UNDRI', '10', NULL

UNION ALL SELECT 'UNDRI', '11', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

UNION ALL SELECT 'UNDRI', '22', NULL

UNION ALL SELECT 'UNDRI', '45', NULL

UNION ALL SELECT 'UNDRI', '20', NULL

; WITH

myCTE (RowNum, Name, Reading, Result) AS

(

SELECT

ROW_NUMBER() OVER (ORDER BY Name, Reading),

Name,

Reading,

Result

FROM @t

)

UPDATE t1 SET t1.Result = t1.Reading - ISNULL(t2.Reading, 0)

FROM myCTE t1

CROSS APPLY (SELECT Reading FROM myCTE WHERE Name = t1.Name AND RowNum = t1.RowNum - 1) t2

--- Finally update the first row which was excluded previously

UPDATE @t SET Result = 0 WHERE Result IS NULL

SELECT * FROM @t

Greets

Flo

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 13

Oh... be careful folks... this wonderful and great interplay of thoughts and ideas to come up with some absolutely outstanding code all share the same common fault... they all can and eventually will produce an incorrect answer because there is nothing in the original posted data nor any of the followup posts to guarantee the order listed in the first post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47068 More actions · Answer 14

Jeff Moden (4/11/2009)
Oh... be careful folks... this wonderful and great interplay of thoughts and ideas to come up with some absolutely outstanding code all share the same common fault... they all can and eventually will produce an incorrect answer because there is nothing in the original posted data nor any of the followup posts to guarantee the order listed in the first post.

Agreed, that is why I said

Also, it is kind of unreliable without a row id to start with...

But as an exercise in teaching some different techniques it is still worthwhile. 🙂

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);