Oracle Prior date Query

Question

I am having problem in fetching the distinct previous reportign date from my date dimension. I need to populate 12 previous reportign dates(distinct) for a given calendar date. This reporting date is populated on caertian business rules. For instnace

CAL_DATE    RPT_DATE
22-Nov-14   24-Nov-14
23-Nov-14   24-Nov-14
24-Nov-14   24-Nov-14
25-Nov-14   25-Nov-14
26-Nov-14   26-Nov-14
27-Nov-14   1-Dec-14
28-Nov-14   1-Dec-14
29-Nov-14   1-Dec-14
30-Nov-14   1-Dec-14
1-Dec-14    1-Dec-14
2-Dec-14    2-Dec-14
3-Dec-14    3-Dec-14
4-Dec-14    4-Dec-14
5-Dec-14    5-Dec-14
6-Dec-14    8-Dec-14
7-Dec-14    8-Dec-14

o/p I am looking for is

DAT_DATE_   RPT_DT  PRIOR1  PRIOR12 PRIOR3  PRIOR4  PRIOR5  PRIOR6
4-Dec-14    3-Dec-14    2-Dec-14    1-Dec-14    26-Nov-14   25-Nov-14   24-Nov-14   21-Nov-14
3-Dec-14    2-Dec-14    1-Dec-14    26-Nov-14   25-Nov-14   24-Nov-14   21-Nov-14   20-Nov-14
The query i used for this is 
select DAT_DATE_DT,dat_sls_wrk_rpt_dt,Prior1,Prior2,Prior3,Prior4,Prior5,Prior6,Prior7,Prior8,Prior9,Prior10  from (
  select DAT_DATE_DT,
  dat_sls_wrk_rpt_dt,
  lag(dat_sls_wrk_rpt_dt,1)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior1,
  lag(dat_sls_wrk_rpt_dt,2)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior2,
  lag(dat_sls_wrk_rpt_dt,3)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior3,
  lag(dat_sls_wrk_rpt_dt,4)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior4,
  lag(dat_sls_wrk_rpt_dt,5)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior5,
  lag(dat_sls_wrk_rpt_dt,6)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior6,
  lag(dat_sls_wrk_rpt_dt,7)over (partition by DAT_DATE_DT order by dat_sls_wrk_rpt_dt ) AS Prior7,
  RANK 
FROM
  ( SELECT DISTINCT B.DAT_DATE_DT,
  A.dat_sls_wrk_rpt_dt,
  dense_rank() over (partition by B.DAT_DATE_DT order by A.dat_sls_wrk_rpt_dt DESC) RANK
  FROM EWT_DATE_DIM1 A,
    EWT_DATE_DIM1 B
  WHERE A.dat_sls_wrk_rpt_dt < B.dat_sls_wrk_rpt_dt
  AND B.DAT_DATE_DT in (trunc(sysdate-15),trunc(sysdate-16) )
  )
WHERE RANK <12)
where RANK=1
ORDER BY 2 DESC

Can anyone think something simpler than this. As there can be many dates for the historic loads and I want to fine tune this. Also, i need this to be done in informatica.

Answer 1

You can do something like this:

declare dCursor CURSOR for  
select dat_date_dt, dat_sql_wrk_rpt_dt 
from EWT_DATE_DIM1
where EWT_DATE_DIM1.DAT < @dt
order by dat_date_dt desc

Then just collect the distinct values. This will be more efficient, especially with and index on dat_date_dt