Solved

Oracle 11g date query (another)

Posted on 2011-02-10
6
706 Views
Last Modified: 2013-11-11
I have this Oracle table:
test2:
select * from test2 order by f0, f1, f3
F0  F1    F2    F3
====================
A      X      7      6/10/2010
A      X      10      6/12/2010
A      Y      20      6/11/2010
A      Y      40      6/17/2010
A      Y      400      6/20/2010
B      P      7      6/10/2010
B      P      10      6/12/2010
B      Q      20      6/11/2010
B      Q      40      6/17/2010
B      Q      400      6/20/2010
C      R      7      6/10/2010
C      R      10      6/12/2010
C      S      20      6/11/2010
C      S      40      6/17/2010
C      S      400      6/20/2010


I want this O/P:
F0      F1       F2      F3
=================================
A      X      7      6/10/2010
A      X      7      6/11/2010
A      X      17      6/12/2010
A      Y      20      6/11/2010
A      Y      20      6/12/2010
A      Y      20      6/13/2010
A      Y      20      6/14/2010
A      Y      20      6/15/2010
A      Y      20      6/16/2010
A      Y      60      6/17/2010
A      Y      60      6/18/2010
A      Y      60      6/19/2010
A      Y      460      6/20/2010
B      P      7      6/10/2010
B      P      7      6/11/2010
B      P      17      6/12/2010
B      Q      20      6/11/2010
B      Q      20      6/12/2010
B      Q      20      6/13/2010
B      Q      20      6/14/2010
B      Q      20      6/15/2010
B      Q      20      6/16/2010
B      Q      60      6/17/2010
B      Q      60      6/18/2010
B      Q      60      6/19/2010
B      Q      460      6/20/2010
C      R      7      6/10/2010
C      R      7      6/11/2010
C      R      17      6/12/2010
C      S      20      6/11/2010
C      S      20      6/12/2010
C      S      20      6/13/2010
C      S      20      6/14/2010
C      S      20      6/15/2010
C      S      20      6/16/2010
C      S      60      6/17/2010
C      S      60      6/18/2010
C      S      60      6/19/2010
C      S      460      6/20/2010

I tried this code:
SELECT LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f0) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f0,
      LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f1,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f2) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f2,
       f3 - COLUMN_VALUE f3
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f0, f1 ORDER BY f3) prevf3
          FROM test2 t) x,
       TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)
ORDER BY f0,f1,f3

But it is returning no value.
It is similar to the qn at http://www.experts-exchange.com/Database/Oracle/Q_26811329.html#a34868122


create table test2(f0 varchar2(100), f1 varchar2(100), f2 number(10), f3 date);
insert into test2 values ('A',  'X', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'X', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('A',  'Y', 400, to_date('06/20/2010', 'mm/dd/yyyy'));

insert into test2 values ('B',  'P', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'P', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('B',  'Q', 400, to_date('06/20/2010', 'mm/dd/yyyy'));

insert into test2 values ('C',  'R', 7, to_date('06/10/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'R', 10, to_date('06/12/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 20, to_date('06/11/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 40, to_date('06/17/2010', 'mm/dd/yyyy'));
insert into test2 values ('C',  'S', 400, to_date('06/20/2010', 'mm/dd/yyyy'));
commit;

Open in new window

0
Comment
Question by:toooki
  • 4
  • 2
6 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 475 total points
ID: 34870294
you need an analytic sum on f2 first, then apply the rest of the logic as before


SELECT LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f0) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f0,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f1) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f1,
       LAST_VALUE(
           DECODE(COLUMN_VALUE, 0, f2) IGNORE NULLS
       )
       OVER (
           PARTITION BY f0, f1
           ORDER BY f3 - COLUMN_VALUE
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       )
           f2,
       f3 - COLUMN_VALUE f3
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f0, f1 ORDER BY f3) prevf3
          FROM (SELECT f0,
                       f1,
                       SUM(
                           f2
                       )
                       OVER (
                           PARTITION BY f0, f1
                           ORDER BY f3
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       )
                           f2,
                       f3
                  FROM test2) t) x,
       TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)
ORDER BY f0, f1, f3
0
 

Author Comment

by:toooki
ID: 34881141
Thank you. I am testing the query.
0
 

Author Comment

by:toooki
ID: 34881315
Thank you. I think I am getting the correct O/P. But could you please let me know these...
I understand:
select level from dual connect by level < 10 Returns 1,2,3 ..9.
         
prevf3 is of DATE data type. How does this part of the below code work:
prevf3 + LEVEL - 1
Why LEVEL - 1 ?

  TABLE(SELECT COLLECT(LEVEL - 1)
               FROM DUAL
             CONNECT BY prevf3 + LEVEL - 1 < f3)

Here is three consecutive row entry (part of) o/p of attached code:
f1      f2     f3              prevf3
B      7      6/10/2010      (NULL)
B      338      6/11/2010      6/10/2010
B      426      6/17/2010      6/11/2010

What is the select value of the above statement for these rows? And what the TABLE keyword is doing?
I am referring to the test1 table:
select * from test1:
f1      f2     f3
A      7      6/10/2010
A      338      6/11/2010
A      426      6/17/2010
B      7      6/10/2010
B      338      6/11/2010
B      426      6/17/2010
C      7      6/10/2010
C      338      6/11/2010
C      426      6/17/2010



SELECT t.*, LAG(f3) OVER (PARTITION BY f1 ORDER BY f3) prevf3
          FROM test1 t

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:toooki
ID: 34881321
Also for this part of code:
  FROM (SELECT t.*, LAG(f3) OVER (PARTITION BY f1 ORDER BY f3) prevf3
          FROM test1 t) x,
 TABLE(SELECT COLLECT(LEVEL - 1)
              FROM DUAL
            CONNECT BY prevf3 + LEVEL - 1 < f3)

Are we "joining" x with
 TABLE(SELECT COLLECT(LEVEL - 1)
              FROM DUAL
            CONNECT BY prevf3 + LEVEL - 1 < f3)

without where clause?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34882646
The level -1 is because we don't really want 1-10,  we want 0-9

the f3 column in the final results is calculated by     f3 - COLUMN_VALUE ,  so,  if we used 1-10,  the end date of each range would be excluded,  f3-1,  but f3-0 = f3, so we get the entire range.
I could have just as easily left it LEVEL,  and then put the -1 on the calculation as "f3 - (column_value - 1)"

--------

compare these results of these two...

select level from dual connect by level <= 10;
select collect(level) from dual connect by level <= 10;

The table function lets us turn the collection into a set we can join against.

-----------------

The select from test table is this, to generate the rolling totals for each date


(SELECT f0,
                       f1,
                       SUM(
                           f2
                       )
                       OVER (
                           PARTITION BY f0, f1
                           ORDER BY f3
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                       )
                           f2,
                       f3
                  FROM test2)


----------------------------------

The join to the TABLE(....)  function does not have a WHERE clause, but it does have a filtering/join clause determined by the CONNECT BY

You'll see the connect by range isn't a fixed 1-10,  the numeric range is determined by the number of days between prevf3 and f3






0
 

Author Comment

by:toooki
ID: 34903521
Many thanks for the explanations. It took me long to understand. I never learnt this much from a single query.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now