Temporary Table

Nura · #1 (**permalink**) 11-15-05, 03:13

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.

madafaka · #2 (**permalink**) 11-15-05, 05:20

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...

Nura · #3 (**permalink**) 11-15-05, 07:09

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

madafaka · #4 (**permalink**) 11-15-05, 07:24

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.

Nura · #5 (**permalink**) 11-15-05, 07:37

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.

madafaka · #6 (**permalink**) 11-15-05, 07:52

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)

Nura · #7 (**permalink**) 11-15-05, 08:02

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

madafaka · #8 (**permalink**) 11-15-05, 08:10

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
                              )
                );

Nura · #9 (**permalink**) 11-15-05, 09:07

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

madafaka · #10 (**permalink**) 11-15-05, 10:13

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
                              )
                );

Internet Services

Web Development

Digital Marketing

Consumer Tech