Results 1 to 10 of 10

Thread: Temporary Table

  1. #1
    Join Date
    Oct 2005
    Posts
    21

    Unanswered: Temporary Table

    I am asked to create a temporary table to solve the below problem in plsql but i am not clear about the concepts.The scenario is


    table 1:
    SUBSCRIBER_ID EFFECTIVE_DATE TERM_DATE PROVIDER

    1000 OCT-31-2004 DEC-31-2004 A1024
    1000 JAN-01-2005 OCT-31-2005 A1024
    1000 NOV-01-2005 DEC-18-2005 A1124
    1000 DEC-19-2005 DEC-31-2005 B1245
    1002 DEC-19-2005 DEC-31-2005 1299

    table 2:

    PROVIDER PROVIDER NAME

    A1024 abc
    A1124 xyz
    B1245 rst
    1299 qwe

    The condition is that i need to write a query in plsql to extract data of provider ie provider name from table 2 and SUBSCRIBER_ID EFFECTIVE_DATE from table 1 where the EFFECTIVE_DATE has a value between Dec18 and Dec 28 2005,and there is a change in provider from the just previous entry of same subscriber.A temporary table has to created to check whether there is a change in provider during the period.

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Code:
    CREATE TABLE tmp_provider AS
    SELECT 
         t2.PROVIDER_NAME,
         t1.SUBSCRIBER_ID,
         t1.EFFECTIVE_DATE
    FROM
         table1 t1,
         table2 t2
    WHERE t1.PROVIDER = t2.PROVIDER
    AND to_date(EFFECTIVE_DATE) between to_date('18-DEC-05') and to_date('28-DEC-05')
    AND ....
    the last condition will define if "there is a change in provider from the just previous entry of same subscriber".
    but I'm not 100% clear what you mean by this sentence. provide more details...

  3. #3
    Join Date
    Oct 2005
    Posts
    21

    Exclamation Temporary Table

    If you look into the fourth row of the table1 you could see that there is a change in the provider.There may be cases where there will not be any change for the provider as in the first 3 rows of table1.
    What i need is a query to extract PROVIDER_NAME,SUBSCRIBER_ID,EFFECTIVE_DATE where temporary table is used to check if there is a change in provider in the period for a member.
    If i am wrong in using Temporary table to check please guide me in sorting the problem

    Thank You

  4. #4
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    when I look at row 4 of table1 all see is:

    1000 DEC-19-2005 DEC-31-2005 B1245

    I can't see if provider has changed or not. how you know there's a change in provider? is it becouse EFFECTIVE_DATE is between 18-DEC and 28-DEC? realise I'm not familiar with your data and your business logic. when I look at 1 row in the table I can't see things you can.

  5. #5
    Join Date
    Oct 2005
    Posts
    21

    Temporary Table

    The provide change can be found by looking at the fourth field in table1.
    The third row has A1124 and fourth row has B1245.This shows that the provider was changed in the period.

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    OK,
    and you need select Provider Name, Subsriber ID and Effective Date

    only where this change occured?
    Code:
    1000    DEC-19-2005    DEC-31-2005    B1245    rst
    or for all data
    Code:
    1000    DEC-19-2005    DEC-31-2005    B1245    rst
    1002    DEC-19-2005    DEC-31-2005    1299     qwe
    I don't understand your crieria:
    EFFECTIVE_DATE between 18-DEC-05 and 28-DEC-05
    becouse this will exclude old records anyway, so you'll receive only latest version of data (latest provider fro specific subscriber)

  7. #7
    Join Date
    Oct 2005
    Posts
    21

    Temp Table

    Dear Friend,

    The table that i gave was a scenario.consider the value B1245 in fourth row was A1124 then the output should be

    1002 DEC-19-2005 DEC-31-2005 1299 qwe

    This is my Requirement.For this i need to write a query in which temp table is created only to check whether there is any change to the provider.If there is change then the provider detail should be in output

    Thank You

  8. #8
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    Try this: (I couldn't test it as I'm not on database)
    Code:
    SELECT 
         t2.PROVIDER_NAME,
         t1.SUBSCRIBER_ID,
         t1.EFFECTIVE_DATE
    FROM
         table1 t1,
         table2 t2
    WHERE t1.PROVIDER = t2.PROVIDER
    AND to_date(EFFECTIVE_DATE) between to_date('18-DEC-05') and to_date('28-DEC-05')
    AND t1.rowid not in (select rowid 
                     from table1 a
                     where exists (select 1
                                   from table1 b
                                   where b.SUBSCRIBER_ID = a.SUBSCRIBER_ID
                                   and   to_date(b.TERM_DATE) = to_date(a.EFFECTIVE_DATE - 1)
                                   and   b.PROVIDER != a.PROVIDER
                                  )
                    );

  9. #9
    Join Date
    Oct 2005
    Posts
    21

    Temporary table prob

    Thanks Dear Friend
    It worked,but there is a problem .As per the scenario that i gave the date diff between term_date and next EFFECTIVE_DATE is 1.But in certain cases in th orginal data base i found that the date diff varies.I found it when your query was run.Could you kindly guide me in it.

    Thank You

  10. #10
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    This could fix your issue. First of all test it. Once it'll work I'll explain what's going on, if you won't understand (sorry about that, but I'm busy now)
    Code:
    SELECT 
         t2.PROVIDER_NAME,
         t1.SUBSCRIBER_ID,
         t1.EFFECTIVE_DATE
    FROM
         table1 t1,
         table2 t2
    WHERE t1.PROVIDER = t2.PROVIDER
    AND to_date(EFFECTIVE_DATE) between to_date('18-DEC-05') and to_date('28-DEC-05')
    AND t1.rowid not in (select rowid 
                     from table1 a
                     where exists (select 1
                                   from table1 b
                                   where b.SUBSCRIBER_ID = a.SUBSCRIBER_ID
                                   and   to_date(b.TERM_DATE) = (select to_date(max(TERM_DATE)) 
                                                                  from table1 c 
                                                                  where c.TERM_DATE < to_date(a.EFFECTIVE_DATE)
                                                                  and c.SUBSCRIBER_ID = b.SUBSCRIBER_ID
                                                                  and c.PROVIDER = b.PROVIDER
                                                                 )
                                   and   b.PROVIDER != a.PROVIDER
                                  )
                    );
    Last edited by madafaka; 11-15-05 at 11:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •