need help in sql

Question

need help in sql

tabrez.test

SSC-Addicted

Points: 459
More actions
February 27, 2010 at 9:59 am

#230324

Hi
plzz help me in sql
in the following table
policy transaction
1234 new
1234 ren
1234 can
1111 new
1111 add
1111 can
2222 new
2222 add
2222 ren
2222 can
3333 new
3333 ren
3333 oth
3333 add
3333 can
4444 new
4444 ren
4444 add
4444 ren
4444 can
the out put should be the all policies where "add" transaction occured and then immediate next "can" transaction occured
so the out put is 1111 & 3333
can any one help me in sql to achieve the above result???

Viewing 15 posts - 1 through 15 (of 15 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

I would use a self join on a numbered CTE.

I don't know how you determine the row that's "immediate next" so I assumed a id column. The reason why I used a cte even if there is an id column is simple: it cannot be guaranteed to have ID values without any gaps.

Also please note how I posted the sample data. Makes it a lot easier to work on. Some of the folks around may not take the time to set up the sample data but rather move on to the next thread (sometimes I do, too). So you'll increas the number of people if you provide ready to use sample data.

DECLARE @tbl TABLE ( id INT IDENTITY(1,1),policy INT, trans CHAR(3))

INSERT INTO @tbl

SELECT 1234 ,'new' UNION ALL

SELECT 1234 ,'ren' UNION ALL

SELECT 1234 ,'can' UNION ALL

SELECT 1111 ,'new' UNION ALL

SELECT 1111 ,'add' UNION ALL

SELECT 1111 ,'can' UNION ALL

SELECT 2222 ,'new' UNION ALL

SELECT 2222 ,'add' UNION ALL

SELECT 2222 ,'ren' UNION ALL

SELECT 2222 ,'can' UNION ALL

SELECT 3333 ,'new' UNION ALL

SELECT 3333 ,'ren' UNION ALL

SELECT 3333 ,'oth' UNION ALL

SELECT 3333 ,'add' UNION ALL

SELECT 3333 ,'can' UNION ALL

SELECT 4444 ,'new' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'add' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'can'

;WITH cte AS

(

SELECT

ROW_NUMBER() OVER(ORDER BY id) AS ROW,

policy,

trans

FROM @tbl

)

SELECT t1.policy

FROM cte t1

LEFT OUTER JOIN cte t2 ON t1.row=t2.row-1

WHERE t1.trans='add' AND t2.trans='can'

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]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Din not see imu92's solution before posting mine .. Imu sorry about that ...

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 3

bitbucket-25253 (2/27/2010)
Din not see lmu92's solution before posting mine .. lmu sorry about that ...

Nothing to be sorry for!

If it's any different than mine you should keep it posted. Two solutions are better than one. 😉

If it's almost identical, well, then I guess I did "win" this time. 😀

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]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Lutz you WIN

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

tabrez.test (2/27/2010)
Hi
plzz help me in sql
in the following table
policy transaction
1234 new
1234 ren
1234 can
1111 new
1111 add
1111 can
2222 new
2222 add
2222 ren
2222 can
3333 new
3333 ren
3333 oth
3333 add
3333 can
4444 new
4444 ren
4444 add
4444 ren
4444 can
the out put should be the all policies where "add" transaction occured and then immediate next "can" transaction occured
so the out put is 1111 & 3333
can any one help me in sql to achieve the above result???

Just to emphasize what Lutz stated, unless you have something to preserve the order of rows you have indicated, this task is doomed to failure because there is no guarantee of what the order of a SELECT will be without an ORDER BY in one form or another. Since the data you posted is not in sorted order by the columns you've included, there must be another column available in your database to ensure the correct order.

Do you have such a column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 6

This is just for fun:

USE tempdb;

GO

DECLARE @SampleData

TABLE (

id INTEGER IDENTITY NOT NULL,

policy INTEGER NOT NULL,

trans CHAR(3) COLLATE LATIN1_GENERAL_BIN NOT NULL,

next_id AS (id + 1)

UNIQUE (trans, next_id, policy),

UNIQUE (trans, id),

PRIMARY KEY (id)

);

INSERT @SampleData (policy, trans)

SELECT 1234 ,'new' UNION ALL

SELECT 1234 ,'ren' UNION ALL

SELECT 1234 ,'can' UNION ALL

SELECT 1111 ,'new' UNION ALL

SELECT 1111 ,'add' UNION ALL

SELECT 1111 ,'can' UNION ALL

SELECT 2222 ,'new' UNION ALL

SELECT 2222 ,'add' UNION ALL

SELECT 2222 ,'ren' UNION ALL

SELECT 2222 ,'can' UNION ALL

SELECT 3333 ,'new' UNION ALL

SELECT 3333 ,'ren' UNION ALL

SELECT 3333 ,'oth' UNION ALL

SELECT 3333 ,'add' UNION ALL

SELECT 3333 ,'can' UNION ALL

SELECT 4444 ,'new' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'add' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'can';

SELECT This.policy, This.trans

FROM @SampleData This

JOIN @SampleData Following

ON Following.id = This.next_id

AND Following.trans = 'can'

WHERE This.trans = 'add'

OPTION (RECOMPILE);

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tabrez.test SSC-Addicted Points: 459 More actions · Answer 7

Just to emphasize what Lutz stated, inless you have something to preserve the order of rows you have indicated, this task is doomed to failure because there is no guarantee of what the order of a SELECT will be without an ORDER BY in one form or another. Since the data you posted is not in sorted order by the columns you've included, there must be another column available in your database to ensure the correct order.

Do you have such a column?[/quote]

thanks for reply.to all.

yes there is another column trn_id which has numbers 1,2,3 ..10

but i want the sql in case statement as there are many other colums iam getting form other tables so my sql should be like

case when above condition(add next can ) met then 1 else 0

Paul White SSC Guru Points: 150467 More actions · Answer 8

tabrez.test (2/27/2010)
yes there is another column trn_id which has numbers 1,2,3 ..10

Thanks for that information, late as it is 🙁

tabrez.test (2/27/2010)
but i want the sql in case statement as there are many other colums iam getting form other tables so my sql should be like case when above condition(add next can ) met then 1 else 0

I can't make out what you are trying to say here. I won't post again until you can explain yourself clearly.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tabrez.test SSC-Addicted Points: 459 More actions · Answer 9

also ihave tried above sql they are also displaying policies which have trans add and trans can and in between other trans like ren,adj,,,(which should not )

i want policies with has trans ADD and followed by CAN imeediately

tabrez.test SSC-Addicted Points: 459 More actions · Answer 10

tabrez.test (2/27/2010)
also ihave tried above sql they are also displaying policies which have trans add and trans can and in between other trans like ren,adj,,,(which should not )
i want policies with has trans ADD and followed by CAN imeediately

i want policies with has trans ADD and followed by CAN immediately---- condition

i mean i want the above result in a column with 1 and 0

for example when the above condition is met then 1 is displayed in the column or else 0

this is my result

policy column1

------ ------

1234 0 --------above condition not satisfied

1111 1---------above condition satisfied

2222 0

3333 1

4444 0

Paul White SSC Guru Points: 150467 More actions · Answer 11

tabrez.test (2/27/2010)
also ihave tried above sql they are also displaying policies which have trans add and trans can and in between other trans like ren,adj,,,(which should not )

No they don't.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 12

USE tempdb;

GO

DECLARE @SampleData

TABLE (

id INTEGER IDENTITY NOT NULL,

policy INTEGER NOT NULL,

trans CHAR(3) COLLATE LATIN1_GENERAL_BIN NOT NULL,

next_id AS (id + 1)

UNIQUE (trans, next_id, policy),

UNIQUE (trans, id),

PRIMARY KEY (id)

);

INSERT @SampleData (policy, trans)

SELECT 1234 ,'new' UNION ALL

SELECT 1234 ,'ren' UNION ALL

SELECT 1234 ,'can' UNION ALL

SELECT 1111 ,'new' UNION ALL

SELECT 1111 ,'add' UNION ALL

SELECT 1111 ,'can' UNION ALL

SELECT 2222 ,'new' UNION ALL

SELECT 2222 ,'add' UNION ALL

SELECT 2222 ,'ren' UNION ALL

SELECT 2222 ,'can' UNION ALL

SELECT 3333 ,'new' UNION ALL

SELECT 3333 ,'ren' UNION ALL

SELECT 3333 ,'oth' UNION ALL

SELECT 3333 ,'add' UNION ALL

SELECT 3333 ,'can' UNION ALL

SELECT 4444 ,'new' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'add' UNION ALL

SELECT 4444 ,'ren' UNION ALL

SELECT 4444 ,'can';

SELECT This.policy,

column1 =

CASE

WHEN EXISTS

(

SELECT *

FROM @SampleData Following

WHERE Following.id = This.next_id

AND Following.trans = 'can'

)

THEN 1

ELSE 0

END

FROM @SampleData This

WHERE This.trans = 'add'

OPTION (RECOMPILE);

Output:

policy column1

------ -------

1111 1

2222 0

3333 1

4444 0

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

tabrez.test SSC-Addicted Points: 459 More actions · Answer 13

Hi Paul

i have used the following query but its also displaying the policies which have trans in between add and can and policies with only add

SELECT t2.TRN_POLICY ,

column1 =

CASE

WHEN EXISTS

(

SELECT *

FROM tabletrn t1

WHERE t1.TRN_NUMBER=t2.TRN_NUMBER+1

AND t1.TRN_CODE= 'CAN'

)

THEN 1

ELSE 0

END

FROM tabletrn t2

WHERE t2.TRN_CODE = 'ADD'

is there any thing iam missing

Paul White SSC Guru Points: 150467 More actions · Answer 14

tabrez.test (2/28/2010)
i have used the following query but its also displaying the policies which have trans in between add and can and policies with only add

When you say "policies which have trans in between 'add' and 'can'", you need to define 'in between'. If the records are listed in TRN_NUMBER order, there will be nothing between 'add' and 'can' for the records returned by the query.

It is the ORDER in which you are looking at the records that is important. For a different listing order, there may well be records 'in between'.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi