How to compate cross column values

Question

Post reply

How to compate cross column values

sachince61

Old Hand

Points: 309
More actions
July 2, 2013 at 7:12 am

#279378

Hi,
I am having requirement for to check cross column values,
Below is the example table
col1col2col3 col4
121/7/20135/7/2013
128/7/20139/7/2013
129/7/201310/7/2013
1211/7/201312/7/2013
Out put should be as below
col1col2col3 col4 OutPut
121/7/20135/7/20130
128/7/20139/7/20130
129/7/201310/7/20131
1211/7/201312/7/20130
Where two cross date matches there should be 1 else 0
so please help me..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Please can we see full table DDL? If you don't have a column by which the rows can be ordered, this can't be done.

John

sachince61 Old Hand Points: 309 More actions · Answer 2

Hello Thanks for reply

below is script

create table #temp

( col1 int , col2 int , dt1 datetime , dt2 datetime )

insert into #temp

select 1,5,'7/1/2013','7/5/2013'

union

select 1,5,'7/7/2013','7/7/2013'

union

select 1,5,'7/7/2013','7/9/2013'

union

select 1,5,'7/10/2013','7/15/2013'

union

select 1,5,'7/15/2013','7/18/2013'

union

select 1,5,'7/18/2013','7/20/2013'

select * from #temp

drop table #temp

where as output i need extra column in that there should be 1 where cross date matches

means update dt2 should match with below dt1

col1col2dt1 dt2 Output

157/1/20137/5/20130

157/10/20137/15/20130

157/15/20137/18/20131

157/18/20137/20/20131

157/7/20137/7/20130

157/7/20137/9/20130

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

-- This should work but the output does not match your "desired output" table.

-- If you can explain why there is a difference then I'm sure the code can

-- be tweaked to meet your requirements.

DROP table #temp

create table #temp

( col1 int , col2 int , dt1 datetime , dt2 datetime )

insert into #temp

select 1,5,'7/1/2013','7/5/2013'

union

select 1,5,'7/7/2013','7/7/2013'

union

select 1,5,'7/7/2013','7/9/2013'

union

select 1,5,'7/10/2013','7/15/2013'

union

select 1,5,'7/15/2013','7/18/2013'

union

select 1,5,'7/18/2013','7/20/2013';

WITH SequencedData AS (

SELECT *, rn = ROW_NUMBER() OVER(ORDER BY col1, col2, dt1, dt2)

FROM #temp

)

SELECT

s1.*,

[output] = CASE WHEN s2.dt2 = s1.dt1 THEN 1 ELSE 0 END

FROM SequencedData s1

LEFT JOIN SequencedData s2 ON s2.rn+1 = s1.rn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sachince61 Old Hand Points: 309 More actions · Answer 4

Thanks for your reply..

Its very much helpful to me..

But i want to know is there any other way without using ROW_NUMBER() function ??

B'cause i am using server 2000 so its not supported.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

sachince61 (7/2/2013)
Thanks for your reply..
Its very much helpful to me..
But i want to know is there any other way without using ROW_NUMBER() function ??
B'cause i am using server 2000 so its not supported.

Sure...

create table #temp

( col1 int , col2 int , dt1 datetime , dt2 datetime )

insert into #temp

select 1,5,'7/1/2013','7/5/2013'

union

select 1,5,'7/7/2013','7/7/2013'

union

select 1,5,'7/7/2013','7/9/2013'

union

select 1,5,'7/10/2013','7/15/2013'

union

select 1,5,'7/15/2013','7/18/2013'

union

select 1,5,'7/18/2013','7/20/2013';

--WITH SequencedData AS (

SELECT *, rn = IDENTITY(int,1,1)

INTO #SequencedData

FROM #temp

--)

SELECT

s1.*,

[output] = CASE WHEN s2.dt2 = s1.dt1 THEN 1 ELSE 0 END

FROM #SequencedData s1

LEFT JOIN #SequencedData s2 ON s2.rn+1 = s1.rn

DROP TABLE #TEMP

DROP TABLE #SequencedData

But why post in the SQL Server2008 forum section?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

sachince61 Old Hand Points: 309 More actions · Answer 6

sachince61

Old Hand

Points: 309

July 2, 2013 at 8:35 am

#1629136

Thank You 🙂