compare few columns in two rows....

Question

Post reply

compare few columns in two rows....

saidwarak01

SSCommitted

Points: 1577
More actions
March 9, 2010 at 12:59 pm

#219772

Hi Everyone,

I have a table which has few columns like pri_key, p_id, phone, mail, status and I want to compare TOP two rows of a particular p_id and want to know which column is changed like phone or mail for that particular p_id. The pri_key is an identity field.

The create table script and also sample data is available below:

CREATE TABLE DIM_ACTIVE_PER(PRI_KEY INT IDENTITY(1,1) PRIMARY KEY,

P_ID INT,

PHONE VARCHAR(20),

MAIL VARCHAR(20),

STATUS VARCHAR(20))

INSERT INTO DIM_ACTIVE_PER (P_ID,PHONE,MAIL,STATUS)

SELECT 10,'6466','ABC@Y.COM','INACTIVE'

UNION ALL

SELECT 11,'6780','XYZ@Y.COM','ACTIVE'

UNION ALL

SELEC 10,'64687','ABC@Y.COM','INACTIVE'

UNION ALL

SELECT 12,'5578','POP@Y.COM','ACTIVE'

UNION ALL

SELECT 10,'6790','DAD@Y.COM','ACTIVE'

UNION ALL

SELECT 13,'4344','JOHN@Y.COM','INACTIVE'

THE OUTPUT SHOULD BE LIKE BELOW FOR EACH P_ID THE P_KEY IS NOT MANDATORY

P_KEY P_ID OLD_PHONE NEW_PHONE OLD_EMAIL NEW_EMAIL STATUS

5 10 64687 6790 ABC@Y.COM DAD@Y.COM ACTIVE

3 11 NULL 6780 NULL XYZ@Y.COM ACTIVE

4 12 NULL 5578 NULL POP@Y.COM ACTIVE

6 13 NULL 4344 NULL JOHN@Y.COM ACTIVE

Thanks in advance....

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Why do you consider OLD_PHONE = 6790 for P_ID =10 as being "old"?

Shouldn't 6790 and 64687 be switched?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

saidwarak01 SSCommitted Points: 1577 More actions · Answer 2

saidwarak01

SSCommitted

Points: 1577

March 9, 2010 at 1:25 pm

#1130930

Hi Lutz,

First of all thanks for your reply...

yes it should be switched.... i am sorry for typing mistake.

thanks in advance.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Well, then the following should work:

(Side note: it still doesn't show P_ID 13 as ACTIVE though... 😉 )

;WITH cte AS



(

	SELECT  *, ROW_NUMBER() OVER(PARTITION BY P_id ORDER BY pri_key DESC) ROW

	FROM    DIM_ACTIVE_PER

)

SELECT

	cte1.PRI_KEY P_KEY,

	cte1.P_ID P_ID,

	cte2.PHONE OLD_PHONE,

	cte1.PHONE NEW_PHONE,

	cte2.MAIL OLD_EMAIL,

	cte1.MAIL NEW_EMAIL,

	cte1.STATUS

FROM    cte cte1

	LEFT OUTER JOIN cte cte2

		ON cte1.P_ID=cte2.P_ID

		AND cte2.row=2

		AND cte1.row=1

WHERE cte1.row=1

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

saidwarak01 SSCommitted Points: 1577 More actions · Answer 4

saidwarak01

SSCommitted

Points: 1577

March 9, 2010 at 2:17 pm

#1130968

Hi lutz,

Thank you very much and I really appreciate for your immediate response to my problem. I want to know how can I build my skills in writing queries like Common Table Expressions, Correlated queries, subqueries and other complex queries.

Please suggest me any technique..

Thanks a lot once again....

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 5

I frequently use three sources of knowledge (not limited to the stuff you mentioned):

BooksOnline (the SQL Server help system usually installed toegether with SQL Server), this site and a test database on my home PC.

The first one to figure out the syntax and some basic examples, the second one to follow threads, search for sample solutions a.s.o. and the last one to try the samples on my own system, see how they work and "play with it".

The major skill you need is to understand the requirement, break it down into smaller pieces and do some kind of divide 'n conquer.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]