Extracting differences between two columns dataset

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

Extracting differences between two columns dataset

Author

Topic

lasqlhelp
Starting Member

14 Posts

Posted - 2008-10-01 : 00:49:33

Hi,

I have a table in my sql, here is an example (tab separated)

585     name1 chr1    +       1872    3533    3533    3533    6       1872,2041,2475,2837,3083,3315,  1920,2090,2560,2915,3237,3533,  name2

The 10th and 11th columns have information in a comma separated format (not tab).

1872,2041,2475,2837,3083,3315,  1920,2090,2560,2915,3237,3533,

I want to know if there's a sql command I can use to find the difference between the first value of column 10 and the first value in column 11, and the same for the second value of column 10 and the second value of column 11, etc.?

for example
1920-1872
2090-2041
2560-2475
.
.
.
3533-3315

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 01:08:36

[code]SELECT m1.PK,m1.val-m2.Val
FROM (
SELECT t.*,b.Val
FROM Table1 t
CROSS APPLY dbo.ParseValues(t.Col10) b)m1
JOIN (SELECT t1.*,b1.Val
FROM Table1 t1
CROSS APPLY dbo.ParseValues(t.Col11) b1)m2
ON m1.PK=m2.PK[/code]

where PK is primary key of your table

function parsevalues can be found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

lasqlhelp
Starting Member

14 Posts

Posted - 2008-10-01 : 01:21:57

visakh16,

I'm quite new to sql and so i'm just learning, so please bare with me if I ask obscure questions ;). do i need a primary key? and the ParseValues function you directed me to is a bit confusing. I'm not sure I understand. Can I just copy the ParseValues function into my sql and then run what you wrote here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 01:25:50

quote:
Originally posted by lasqlhelp

visakh16,

I'm quite new to sql and so i'm just learning, so please bare with me if I ask obscure questions ;). do i need a primary key? and the ParseValues function you directed me to is a bit confusing. I'm not sure I understand. Can I just copy the ParseValues function into my sql and then run what you wrote here?

Nope. even if you dont have primary key, you can just the column which have unique values in your table instead of PK.
You need to copy and run ParseValues code once in your db before running the given solution.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 01:43:36

as an illustration. i've created a test table with just an id column which is unique values and two columns with csv list data. see this

declare @test table
(id int,
col1 varchar(2000),
col2 varchar(2000)
)
insert into @test
select 585, '1872,2041,2475,2837,3083,3315',  '1920,2090,2560,2915,3237,3533'




SELECT m1.id,m1.Val,m2.Val,m1.val-m2.Val AS Diff
FROM (
SELECT t.*,b.ID as BID,b.Val
FROM @test t
CROSS APPLY dbo.ParseValues(t.Col1) b)m1
JOIN (SELECT t1.*,b1.ID as BID,b1.Val
FROM @test t1
CROSS APPLY dbo.ParseValues(t1.Col2) b1)m2
ON m1.id=m2.id
AND m1.BID=m2.BID


output
------------------------------
id	Val	Val	Diff
585	1872	1920	-48
585	2041	2090	-49
585	2475	2560	-85
585	2837	2915	-78
585	3083	3237	-154
585	3315	3533	-218

lasqlhelp
Starting Member

14 Posts

Posted - 2008-10-01 : 02:14:33

thank you! I have some work to do to understand all this. Thanks :).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 02:17:02

quote:
Originally posted by lasqlhelp

thank you! I have some work to do to understand all this. Thanks :).

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources