SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Date periods

744152Jun 1 2010 — edited Jun 1 2010

Hi all,

I have the following

CREATE TABLE adjustment (
  id NUMBER(19, 0) NOT NULL, 
  startDate DATE,
  interval INTEGER,
  occurrence INTEGER,
  PRIMARY KEY(id)
);

INSERT INTO adjustment VALUES(1, '2010-06-01', 4, 4);
INSERT INTO adjustment VALUES(2, '2010-06-01', 1, 12);

Basically it is a table for adjustment dates that are based on a start date, an interval in months and an occurrence.
What I am trying to achieve is a single query where the results contain the row id and each adjustment interval date. So running the query on the above will result in the following.

1, '2010-06-01'
1, '2010-10-01'
1, '2011-02-01'
1, '2011-06-01'
2, '2010-06-01'
2, '2010-07-01'
2, '2010-08-01'
2, '2010-09-01'
2, '2010-10-01'
... etc

I am fully aware that this is not the best data design in the world, but unfortunately that is out of my control.

I'm not entirely sure if this is possible, and if it is, how to go about it.
Any help would be much appreciated.

Thanks.

Toon Koppelaars

You'll need to give us the example output, so we can understand what it is that you want this query to do.

Kuldeep2

lets take an example.

Suppose we have a data in table as follows our filter is 3 days window

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10
4	04-JUN-10
5	03-JUN-10
6	02-JUN-10
8	31-MAY-10

the query should return following output

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10
4	04-JUN-10
5	03-JUN-10
6	02-JUN-10

-- output does not have record for 31-st may as it does not satisfies our window filter

if we have data in a table a follows

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10
5	03-JUN-10
6	02-JUN-10
8	31-MAY-10

Expected output

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10

because if we go back 3 days from 7th June we have do not have data for 4th June so we can not traverse back from 4th so query should exclude all data after 4th June.

I hope it would help.

lee200

Kuldeep,

If the data was:

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10
4	04-JUN-10
5	03-JUN-10
6	02-JUN-10
7	01-JUN-10
8	31-MAY-10
9	30-MAY-10
11	28-MAY-10

Would the expected result be:

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
2	06-JUN-10
3	05-JUN-10
4	04-JUN-10
5	03-JUN-10
6	02-JUN-10
7	01-JUN-10
8	31-MAY-10
9	30-MAY-10

Kuldeep2

Correct! Exactly,

Basically we need to lookback 3 days.

Kuldeep2

To simply you can take same id's ( would be helpful if considering analytic function)

ID	DT
++++++++++++++++++++++++
1	07-JUN-10
1	06-JUN-10
1	05-JUN-10
1	04-JUN-10
1	03-JUN-10
1	02-JUN-10
1	01-JUN-10
1	31-MAY-10
1	30-MAY-10
1	28-MAY-10

Karthick2003

Will this work

select id, dt
  from (
         select id, dt, ldt, max(ldt) over(order by dt desc) chg
           from (
                  select id, dt, trunc(lag(dt) over(order by dt desc))-trunc(dt) ldt 
                    from t
                )
       )
 where chg = 1 or chg is null

Kuldeep2

It will not work when we have missing data in 3 days window

e.g.

insert into t values (1,sysdate);
insert into t values (1,sysdate-1);
--insert into t values (1,sysdate-2);
insert into t values (1,sysdate-3);
insert into t values (1,sysdate-4);
insert into t values (1,sysdate-5);
insert into t values (1,sysdate-6);
insert into t values (1,sysdate-7);

id     DT
+++++++++++++
1	07-JUN-10
1	06-JUN-10
1	04-JUN-10
1	03-JUN-10
1	02-JUN-10
1	01-JUN-10
1	31-MAY-10

for above scenario,

we do not have any data for 5th June but its in 3 days window from 7th june so query should return
all record.

lee200

Kuldeep,

I think this does what you want. Just replace the two instances of the number 3 with the user specified value:

WITH t AS
(SELECT 1 id, TO_DATE('07-JUN-2010') dt FROM dual UNION ALL
 SELECT 2, TO_DATE('06-JUN-2010') dt FROM dual UNION ALL
 SELECT 3, TO_DATE('05-JUN-2010') dt FROM dual UNION ALL
 SELECT 4, TO_DATE('04-JUN-2010') dt FROM dual UNION ALL
 SELECT 5, TO_DATE('03-JUN-2010') dt FROM dual UNION ALL
 SELECT 6, TO_DATE('02-JUN-2010') dt FROM dual UNION ALL
 SELECT 7, TO_DATE('01-JUN-2010') dt FROM dual UNION ALL
 SELECT 8, TO_DATE('31-MAY-2010') dt FROM dual UNION ALL
 SELECT 9, TO_DATE('30-MAY-2010') dt FROM dual UNION ALL
 SELECT 11, TO_DATE('28-MAY-2010') dt FROM dual)
SELECT id,
       dt
FROM   t
WHERE  dt >= (SELECT MIN(dt) - 3
              FROM  (SELECT dt,
                            FIRST_VALUE(dt) OVER () first_dt,
                            LEAD(dt, 1, TO_DATE('01_JAN-1900')) OVER (ORDER BY dt DESC) lead_dt
                     FROM   t)
              WHERE  MOD(first_dt - lead_dt, 3) = 0)

Aketi Jyuuzou

Wow this is a very interesting question.
Thanks.

Hehe I like OLAP and Hierarchical query B-)

OLAP samples of my homepage :D
http://www.geocities.jp/oraclesqlpuzzle/oracle-sql1-olap.html

WITH t(id,dt) AS
(SELECT 1,date '2010-06-07' FROM dual UNION ALL
 SELECT 2,date '2010-06-06' FROM dual UNION ALL
 SELECT 3,date '2010-06-05' FROM dual UNION ALL
 SELECT 4,date '2010-06-03' FROM dual UNION ALL
 SELECT 5,date '2010-06-02' FROM dual UNION ALL
 SELECT 6,date '2010-05-31' FROM dual)
select id,dt
from (select id,dt,
      max(dt) over() maxdt,
      count(dt) over(order by dt range 2 preceding) as has3
        from t)
start with dt = maxdt
       and has3 = 3
connect by prior dt-1=dt
       and not (mod(Level-1,3)=0 and has3!=3);

ID  DT
--  --------
 1  10-06-07
 2  10-06-06
 3  10-06-05

WITH t(id,dt) AS
(SELECT 1,date '2010-06-07' FROM dual UNION ALL
 SELECT 2,date '2010-06-06' FROM dual UNION ALL
 SELECT 3,date '2010-06-05' FROM dual UNION ALL
 SELECT 4,date '2010-06-04' FROM dual UNION ALL
 SELECT 5,date '2010-06-03' FROM dual UNION ALL
 SELECT 6,date '2010-06-02' FROM dual UNION ALL
 SELECT 7,date '2010-05-31' FROM dual)
select id,dt
from (select id,dt,
      max(dt) over() maxdt,
      count(dt) over(order by dt range 2 preceding) as has3
        from t)
start with dt = maxdt
       and has3 = 3
connect by prior dt-1=dt
       and not (mod(Level-1,3)=0 and has3!=3);

ID  DT
--  --------
 1  10-06-07
 2  10-06-06
 3  10-06-05
 4  10-06-04
 5  10-06-03
 6  10-06-02

662724

Try this

select id, dt
from t
where to_date(dt) between to_date('07-JUN-10')-3 and to-date('07-JUN-10')

BobLilly

Kuldeep,

I think this will give you what you are looking for. I'm not sure if there is a solution using one pass against the data. Maybe recursive subquery factoring in 11gR2 could do it.

with t as ( 
select 1 as id, trunc(sysdate)   as dt from dual union all
select 2 as id, trunc(sysdate)-1 as dt from dual union all
select 3 as id, trunc(sysdate)-2 as dt from dual union all
select 4 as id, trunc(sysdate)-3 as dt from dual union all
select 5 as id, trunc(sysdate)-4 as dt from dual union all
select 6 as id, trunc(sysdate)-5 as dt from dual union all
select 8 as id, trunc(sysdate)-7 as dt from dual
)
select id, dt from t
where dt >= (
  select dt-3 as cutoff_dt
  from t
  where connect_by_isleaf=1
  start with dt=trunc(sysdate)
  connect by dt = (prior dt) - 3
)
order by dt desc
;

Regards,
Bob

1 - 11

Locked Post

New comments cannot be posted to this locked post.

Locked on Jun 29 2010

Added on Jun 1 2010

4 comments

879 views

SQL & PL/SQL

Date periods

Comments

Post Details