expand date column to months

Hi,

i have

D_POSITION:

POSITION_SK         POSITION_TCD     EFF_DT      EXP_DT

10053014            1004305            2003-01-01      2005-08-31
10053015            1004305             2005-09-01           2005-09-01

i want to expand eff_dt as below

POSITION_SK     POSITION_TCD                       eff_dt

10053014               1004305                            2003-01-01
10053014               1004305                            2003-01-02
10053014               1004305                            2003-01-03
so on

10053015               1004305                            2005-09-01
10053015               1004305                            2005-09-02
10053015              1004305                            2005-09-03
and so on

Thanks
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
why do you have 3 values for 2005-09   ?  given your data example looks like you should have only one day
0
sdstuberCommented:
Assuming the sample exp_dt is mistaken...

try this...


SELECT position_sk, position_tcd, eff_dt + COLUMN_VALUE - 1 eff_dt
  FROM d_position,
       TABLE(
               SELECT COLLECT(LEVEL)
                 FROM DUAL
           CONNECT BY LEVEL <= exp_dt - eff_dt + 1
       )
0
Mark GeerlingsDatabase AdministratorCommented:
What exactly do you mean by "expand"?  Do you want some additional rows returned in a report or query?  It should be possible to construct a view to do that for you.  Or, do you actually want additional records to be created and saved?

It looks like you want a record returned (or created?) for every calendar day that is between the "EFF_DT" and the "EXP_DT" for each "POSITION_SK" value.  So your first example from 2003-01-01 through 2005-08-31, would include about 960 records (32 months X 30 days), correct?

But, I'm confused by your second example, since it looks to me like the "EFF_DT" and "EXP_DT" are the same day (2005-09-01) yet you indicate you want records for 09-01, 09-02 and 09-03.  How do we know when to stop?  Or, did you make a mistake in the "EXP_DT" for that one?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
If you have 11gR2 or higher you can build a recursive query


WITH   x(position_sk,
       position_tcd,
       eff_dt,
       exp_dt)
     AS (SELECT position_sk,
                position_tcd,
                eff_dt,
                exp_dt
           FROM d_position
         UNION ALL
         SELECT position_sk,
                position_tcd,
                eff_dt + 1,
                exp_dt
           FROM x
          WHERE eff_dt + 1 <= exp_dt)
  SELECT position_sk, position_tcd, eff_dt
    FROM x
ORDER BY position_sk, position_tcd, eff_dt
0
sam2929Author Commented:
It bit more to this:

S_POS_VACANCY
POSITION_TCD PV_START_DT PV_END_DT VACANCY_STATUS

1004305                   2003-01-01      2004-06-20      Y
1004305                   2003-06-21      999-12-31        N

and then i have  

D_POSITION:

POSITION_SK         POSITION_TCD     EFF_DT      EXP_DT

10053014            1004305            2003-01-01            2005-08-31
10053015            1004305             2005-09-01           2005-09-01
10052018           1004305              2005-09-02           9999-12-31    


For 10004305:
•      According to S_POS_VACANCY, Position 10004305 has been vacant from 2003-01-01 until 2004-06-20, and then filled from 2004-06-21 until 9999-12-31.
•      According to D_POSITION, Position 10004305 for this time (2003-01-01 until 9999-12-31) is represented by 3 entries
 in D_POSITION (POSITION_SK): 10053014, 10053015, 10052018.
•      So, the entries in F_POS_VACANCY or target should be as follows:


POSITION_SK      DATE_SK      VACANCY_FLAG


10053014      20030101      1
10053014      20030102      1
10053014      20030103      1
10053014      20030104      1
10053014      20030105      1
10053014      20030106      1
10053014      20030107      1
(and many more similar entries for EACH DATE until 2004-06-20…)
10053014      20040619      1
10053014      20040620      1
(then the VACANCY_FLAG value switches to 0 …)      
10053014      20040621      0
10053014      20040622      0
10053014      20040623      0
(the POSITION_SK changes after 2005-08-31 and again after 2005-09-01)
10053014      20050831      0
10053015      20060901      0
10052018      20060902      0
(until the current date…)            
10052018      20130911      0
10052018      20130912      0
10052018      20130913      0
0
sdstuberCommented:
going back to original question...

if 12c you can generate a collection with an embedded function

this is all one sql statement


WITH FUNCTION number_list(s IN DATE, e IN DATE)
         RETURN ora_mining_number_nt
     IS
         v_list ora_mining_number_nt := ora_mining_number_nt();
         d      DATE := s;
     BEGIN
         v_list.EXTEND(e - s + 1);

         FOR i IN 1 .. e - s + 1
         LOOP
             v_list(i) := i;
         END LOOP;

         RETURN v_list;
     END;   
SELECT position_sk, position_tcd, eff_dt + COLUMN_VALUE - 1 eff_dt
  FROM d_position, TABLE(number_list(eff_dt, exp_dt))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
And, do you actually want these values to be created and stored back as records in a table?  Or, do you just want a report to include them, even though they don't actually exist as unique records in the table for each date?
0
sdstuberCommented:
please ask new requirements as a new question
0
sdstuberCommented:
Do you need anything further on the original question?

if, as markgeer asked, you simply need to reload these values into table

take any of the queries I posted that are appropriate to your version

and do something like this...

insert into your_new_table(a,b,c)
select ....

change your_new_table to whatever your table name is
change a,b,c to whatever columns you have in your new table
change select .... to be whichever of the queries you picked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.