April 11, 2009 at 4:43 am
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........
April 11, 2009 at 6:01 am
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
--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
April 11, 2009 at 6:50 am
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
April 11, 2009 at 7:05 am
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
April 11, 2009 at 7:22 am
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', '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
; 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
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
April 11, 2009 at 7:45 am
Its really cool...with 3 different solutions..and all of them are independet :):-)
April 11, 2009 at 7:50 am
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.
April 11, 2009 at 10:00 am
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.
April 11, 2009 at 10:23 am
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
April 11, 2009 at 10:27 am
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', '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....
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);April 11, 2009 at 10:36 am
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
April 11, 2009 at 11:03 am
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);April 11, 2009 at 12:10 pm
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', '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
; 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
April 11, 2009 at 2:11 pm
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
Change is inevitable... Change for the better is not.
April 11, 2009 at 2:18 pm
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);Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply