Subtracting adjacent rows. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Subtracting adjacent rows.

Author

Topic

Acoustic1978
Starting Member

7 Posts

Posted - 2007-08-22 : 19:32:34

Hello forum.

How do I go about subtracting adjacent Rows? Let's say each day I entered one row entry into a table (tblFlowerHeight)showing the new height of a flower in one column(Height)...say if I had a second second column (ChangeFromLastHeight), how would I go about showing the difference between current height and the last recorded height?

Please assist.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 03:38:31

Maybe using the ROW_NUMBER() function?

Without knowing more about your tables (tabel DDL/structure) the only thing we can do is guessing.

E 12°55'05.25"
N 56°04'39.16"

Acoustic1978
Starting Member

7 Posts

Posted - 2007-08-23 : 09:23:35

Thanx for the response, Peso.

I have three columns in my table with the following datatypes:

Date - DateTime
Height - Decimal
Change_From_last_Height - Decimal

I am using "SQL Server 2005 Express Edition". I'm fairly new to SQL and would greatly appreciate any help or advice I can get.

The "date" column increases by an extra day in every new row and I then enter the new height of the plant. What I want to know is how I can get SQL server express to automatically enter the difference in height between the current row's height and that of the previous row.

Is it possible to automate the entry in the Change_From_Last_Height column in SQL?

Put another way, I know how to find the difference between two values in the same row but different columns, but how do I calculate the difference between values in adjacent Rows (ie. Rows next to each other)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 10:54:09

[code]-- Prepare sample data
DECLARE @Sample TABLE (PlantID INT, Date DATETIME, Height DECIMAL(10, 4), Change_From_last_Height DECIMAL(10, 4))

INSERT @Sample (PlantID, Date, Height)
SELECT 1, '20070101', 4 UNION ALL
SELECT 2, '20070201', 5 UNION ALL
SELECT 1, '20070301', 6 UNION ALL
SELECT 2, '20070401', 9 UNION ALL
SELECT 2, '20070501', 15 UNION ALL
SELECT 1, '20070601', 27 UNION ALL
SELECT 2, '20070701', 28 UNION ALL
SELECT 1, '20070801', 35

-- Stage the data
-- This code and below is the one you need to copy and paste and adapt to your environment
;WITH stage (PlantID, Date, Height, Inspection)
AS (
SELECT PlantID, Date, Height, ROW_NUMBER() OVER (PARTITION BY PlantID ORDER BY Date)
FROM @Sample
)

select s1.Plantid,
s1.date,
s1.height,
s1.height - s2.height as Change_From_last_Height
from stage as s1
left join stage as s2 on s2.plantid = s1.plantid
and s2.inspection = s1.inspection - 1
order by s1.plantid,
s1.inspection[/code]

E 12°55'05.25"
N 56°04'39.16"

Acoustic1978
Starting Member

7 Posts

Posted - 2007-08-23 : 16:05:31

quote:

Originally posted by Peso

-- Prepare sample data
DECLARE	@Sample TABLE (PlantID INT, Date DATETIME, Height DECIMAL(10, 4), Change_From_last_Height DECIMAL(10, 4))

INSERT	@Sample (PlantID, Date, Height)
SELECT	1, '20070101', 4 UNION ALL
SELECT	2, '20070201', 5 UNION ALL
SELECT	1, '20070301', 6 UNION ALL
SELECT	2, '20070401', 9 UNION ALL
SELECT	2, '20070501', 15 UNION ALL
SELECT	1, '20070601', 27 UNION ALL
SELECT	2, '20070701', 28 UNION ALL
SELECT	1, '20070801', 35

-- Stage the data
-- This code and below is the one you need to copy and paste and adapt to your environment
;WITH stage (PlantID, Date, Height, Inspection)
AS (
	SELECT	PlantID, Date, Height, ROW_NUMBER() OVER (PARTITION BY PlantID ORDER BY Date)
	FROM	@Sample
)

select		s1.Plantid,
		s1.date,
		s1.height,
		s1.height - s2.height as Change_From_last_Height 
from		stage as s1
left join	stage as s2 on s2.plantid = s1.plantid
			and s2.inspection = s1.inspection - 1
order by	s1.plantid,
		s1.inspection

E 12°55'05.25"
N 56°04'39.16"

Wow Peso!

Thank you very much for the advice! With a little twitching here and there to suite it to my particular project, it should work absolutely perfectly.

Once again, thank you very much for the generous demonstration. I was in a bit of a fix and you have no idea how much you've helped.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 16:19:11

I can tell later at PayPal's site

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources