expand date column to months

why do you have 3 values for 2005-09   ?  given your data example looks like you should have only one day

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
       )

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?

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

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

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?

please ask new requirements as a new question

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