Post reply

need help in sql

  • 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???

  • February 27, 2010 at 11:36 am

    #1126120

    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]

  • February 27, 2010 at 11:41 am

    #1126123

    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]

  • February 27, 2010 at 12:00 pm

    #1126128

    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]

  • February 27, 2010 at 12:13 pm

    #1126132

    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]

  • February 27, 2010 at 1:05 pm

    #1126141

    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)
    Intro to Tally Tables and Functions

  • February 27, 2010 at 10:32 pm

    #1126195

    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

  • February 27, 2010 at 10:56 pm

    #1126199

    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

  • February 27, 2010 at 11:06 pm

    #1126202

    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

  • February 27, 2010 at 11:24 pm

    #1126204

    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

  • February 27, 2010 at 11:34 pm

    #1126205

    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

  • February 27, 2010 at 11:45 pm

    #1126206

    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

  • February 28, 2010 at 12:11 am

    #1126212

    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

  • February 28, 2010 at 1:31 am

    #1126221

    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

  • February 28, 2010 at 1:52 am

    #1126223

    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

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

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