Home » SQL & PL/SQL » SQL & PL/SQL » Interesting Query
Interesting Query [message #163495] Thu, 16 March 2006 22:33 Go to next message
jman27
Messages: 37
Registered: September 2005
Member
My head is spinning on this one... This forum has had so many great suggestions I thought I'd toss it out there.

I have a table that holds records for system active times... basically the table looks something like:

STATUS_ID  SYS_ID  START_DATE   END_DATE     STATUS
---------  ------  -----------  -----------  ------
1          10      2006-MAR-01  2006-MAR-07  ON
2          10      2006-MAR-08  2006-MAR-14  ON
3          10      2006-MAR-03  2006-MAR-09  OFF
4          10      2006-MAR-10  2006-MAR-16  OFF
5          15      2006-FEB-01  2006-MAR-31  ON
6          10      2006-FEB-15  2006-MAR-21  ON


As you can see SYS_ID=10 had a few changes throughout time. What I'd *like* to do is query once and receive the start/end dates of each status as it currently stands (1 status change per row). For example, given the above data, if I query for the status of SYS_ID=10 between the dates of 3/2/06 - 3/18/06 I should get something like:

START_DATE   END_DATE     STATUS
-----------  -----------  ------
2006-MAR-02  2006-MAR-02  ON
2006-MAR-03  2006-MAR-14  OFF
2006-MAR-15  2006-MAR-18  ON


One thing that might help in building the query is the fact that a record with a higher STATUS_ID should get priority since it was entered more recently (STATUS_ID is incremented with each insert). Also note that the first record's START_DATE is 3/2 and not 3/1 (since I was asking for 3/2 - 3/18). The same goes for the END_DATE on the last record.

Then to really test things you could add a STATUS_ID=7 where you turn SYS_ID=10 back on between the dates 3/11 - 3/13. The result set should then look like:

START_DATE   END_DATE     STATUS
-----------  -----------  ------
2006-MAR-02  2006-MAR-02  ON
2006-MAR-03  2006-MAR-10  OFF
2006-MAR-11  2006-MAR-13  ON
2006-MAR-14  2006-MAR-14  OFF
2006-MAR-15  2006-MAR-18  ON


It isn't absolutely necessary to set the START_DATE and END_DATE to the start/end dates in the query (3/2/06 - 3/18/06)... that was only going to keep things simple in the code. If it can be done, great. If not, this would be acceptable:

START_DATE   END_DATE     STATUS
-----------  -----------  ------
2006-MAR-01  2006-MAR-02  ON
2006-MAR-03  2006-MAR-10  OFF
2006-MAR-11  2006-MAR-13  ON
2006-MAR-14  2006-MAR-14  OFF
2006-MAR-15  2006-MAR-21  ON



I could just do a simple select and build an array of dates in the code... but I'm trying to avoid that. Any assistance would be greatly appreciated. Thanks in advance...

Josh

[Updated on: Fri, 17 March 2006 15:59]

Report message to a moderator

Re: Interesting Query [message #225175 is a reply to message #163495] Sun, 18 March 2007 18:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I made a little assumption in your data.

I presume the entry number 6 should be 15th march 2007 and not 15th Feb 2007.

6          10      2006-FEB-15  2006-MAR-21  ON


It is bit of a messy sql but it works. Let me know your comments.


SQL> desc system_data;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 STATUS_ID                                          NUMBER
 SYS_ID                                             NUMBER
 START_DATE                                         DATE
 END_DATE                                           DATE
 STATUS                                             VARCHAR2(3)

SQL> select * from system_data;

 STATUS_ID     SYS_ID START_DATE  END_DATE    STA
---------- ---------- ----------- ----------- ---
         1         10 01-MAR-2007 07-MAR-2007 ON
         2         10 08-MAR-2007 14-MAR-2007 ON
         3         10 03-MAR-2007 09-MAR-2007 OFF
         4         10 10-MAR-2007 14-MAR-2007 OFF
         5         15 01-FEB-2007 31-MAR-2007 ON
         6         10 15-MAR-2007 21-MAR-2007 ON
         7         10 11-MAR-2007 13-MAR-2007 ON
o/p is

MIN(START_D MAX(END_DAT MAX
----------- ----------- ---
02-MAR-2007 02-MAR-2007 ON
03-MAR-2007 10-MAR-2007 OFF
11-MAR-2007 13-MAR-2007 ON
14-MAR-2007 14-MAR-2007 OFF
15-MAR-2007 18-MAR-2007 ON

with sd as
(select sys_id, status_id, start_date, end_date, status from
system_Data where sys_id = 10),
date_range as
(
select st_date, end_Date from (select to_date(:start_Date,'DD-MM-YYYY')
+ level - 1 st_date, to_Date(:start_date,'DD-MM-YYYY') - 1 + level
end_date from dual
connect by level <= to_date(:end_Date,'DD-MM-YYYY') -
to_date(:start_Date,'DD-MM-YYYY') + 1))
select min(start_date), max(end_date), max(status) from
(
  select start_date, end_date, status, max(grp) over(order by start_date) grp2
  from
  (
    select start_date, end_date, status, max_status_id,
       case
        when lag(status) over(order by start_date) != status
        or
        row_number() over(order by start_date) = 1
       then
        row_number() over(order by start_date)
       end grp
    from
    (
      select start_Date, end_date, status, status_id, max(status_id) over(partition by start_date order by start_date) max_status_id       
      from
      (
         select dr.st_date start_date, dr.end_date end_date, sd.status, sd.status_id
         from date_range dr, sd 
         where
         (
            dr.st_date >= sd.start_date
            and
            dr.end_date <= sd.end_date
         )
       )
     ) where status_id = max_status_id
   )
) group by grp2
order by 1


Not too sure how performant this query is.

cheers
Re: Interesting Query [message #225177 is a reply to message #225175] Sun, 18 March 2007 20:33 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
This is over a year old post, I surely hope the OP has solved his problem by now. I seriously doubt you'll hear back from the OP.
Re: Interesting Query [message #225218 is a reply to message #225177] Mon, 19 March 2007 03:30 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Ooops.. thanks for letting me know. I never realised it...
Previous Topic: Table Transactions (Type &Time)
Next Topic: query help
Goto Forum:
  


Current Time: Mon Dec 05 21:24:54 CST 2016

Total time taken to generate the page: 0.06503 seconds