• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

Oracle 11g date query (another)

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
toooki
Asked:
toooki
  • 4
  • 2
1 Solution
 
sdstuberCommented:
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
 
toookiAuthor Commented:
Thank you. I am testing the query.
0
 
toookiAuthor Commented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
toookiAuthor Commented:
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
 
sdstuberCommented:
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
 
toookiAuthor Commented:
Many thanks for the explanations. It took me long to understand. I never learnt this much from a single query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now