Iterative Subtracting - 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

General SQL Server Forums

New to SQL Server Programming

Iterative Subtracting

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-08 : 12:00:06

subtracting values

Table1:
Name Value
A 234
B 534
C 546
D 923

Result Table

Name Value Difference
A 234 534-234
B 534 546-534
C 546 NULL
D 923 NULL

Also 923-546 = 377. Since 300 is the standard - we want to show that as null.
300 is the standard.

How can I do this?

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-08 : 12:09:32

;WITH z(name, VALUE, rn) AS (SELECT name, VALUE, ROW_NUMBER() OVER (ORDER BY name) FROM Table1)
SELECT a.name, a.value,
CASE WHEN b.value-a.value>300 THEN NULL ELSE CAST(b.value AS VARCHAR) + '-' + CAST(a.value AS VARCHAR) END diff
FROM z a
LEFT JOIN z b ON a.rn=b.rn-1

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-08 : 12:58:11

Hey Rob,

Can I only reference a column? Also how would I reference a derived column?
For Example:
I give the alias of "VALUE" to the column. I cannot reference VALUE in my function correct? I only want to reference value create a new column called Difference.

Also Im new to sql. why the letters z and a?

Thanks so much already
Dasman

==========================
Pain is Weakness Leaving the Body.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-08 : 13:29:46

A and B are aliases, and Z is the name for the CTE. You can reference a derived column using a subquery, or since I've already got a CTE, I can stack another one:

;WITH z(name, value, rn) AS (SELECT name, value, ROW_NUMBER() OVER (ORDER BY name) FROM Table1),
Diff (name, value, difference) AS (SELECT a.name, a.value, 
CASE WHEN b.value-a.value>300 THEN NULL ELSE CAST(b.value AS VARCHAR) + '-' + CAST(a.value AS VARCHAR) END diff 
FROM z a LEFT JOIN z b ON a.rn=b.rn-1)
SELECT name, value, difference FROM Diff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources