top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle Prior date Query

0 votes
167 views

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.

posted Dec 29, 2014 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

answer Dec 30, 2014 by Shweta Singh
Similar Questions
+3 votes

SELECT EMP_NAME, SSN FROM EMP WHERE HIER_DATE = TO_DATE('03-NOV-1999','DD-MON-YYYY');

+1 vote

Hi Friends,
I saw somewhere that MME will also do a DNS query for P-GW. As i know S-Gw will do a query for P-Gw address. Is there any situation that MME will do a query for P-GW address using APN? and after this query how MME will use the P-Gw Address.

and is there any possibility of Doing DNS query by P-Gw to get any other nodes Ips and under what conditions P-Gw will do a DNS query?

+2 votes

I want to display the time stamp in following format '2011-04-22 10:41:57.000' to date as '04/22/2011' but when it convert it to following form TO_DATE(TO_CHAR(Date), 'MM/DD/YYYY HH24:MI:SS') it is displaying as null.

I am planning to use substring after the conversion

Can someone please tell me where i am going wrong?

+3 votes

How to calculate the Monday date of a week using the week number in Informatica?!

...