Skip to Main Content
Forums

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.

Traversing back 3 days (window) in SQL

Kuldeep2Jun 7 2010 — edited Nov 3 2010
create table t ( id integer, dt date)
insert into t values (1,sysdate);
insert into t values (2,sysdate-1);
insert into t values (3,sysdate-2);
insert into t values (4,sysdate-3);
insert into t values (5,sysdate-4);
insert into t values (6,sysdate-5);
insert into t values (8,sysdate-7);

I have data as follows.
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
Now i need to write a query to return data which comes under 3 days (thois could be 4- its a user input) window.

e.g.

Query will start from 07 June and will go back 3 days i.e. till 4th June, now since there is data for 4th june it will go back till 1st June. Now we do not have data for 1-Jun so it will stop here and query will return all record except for 31st May.

I do not have choice of writing procedure here. can someone help.

Comments

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.

Post Details

Locked on Jul 5 2010
Added on Jun 7 2010
11 comments
3,122 views