Home » SQL & PL/SQL » SQL & PL/SQL » Complex Days Count In One SQL (Oracle 8)
Complex Days Count In One SQL [message #293170] Fri, 11 January 2008 01:34 Go to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
Dear all,

Please help me on this problem.

I have 2 tables as follow:

base_period (
primary_key varchar2(10),
from_date date,
to_date date)

exclude_period (
foreign_key varchar2(10),
from_date date,
to_date date)

I want to have one single SQL to select the number of days
between base_period.from_date and base_period.to_date for
a particular base_period.primary_key, excluding days
between exclude_period.from_date and exclude_period.to_date
where exclude_period.foreign_key = base_period.primary_key.
Relation of base_period to exclude_period is 1 to many.
Records of exclude_period may be overlapping, overlapped days
are count once only.

Thanks in advance.
Ming

[Updated on: Fri, 11 January 2008 02:38]

Report message to a moderator

Re: Complex Days Count In One SQL [message #293183 is a reply to message #293170] Fri, 11 January 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post a test case: create table and insert statements.

Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: Complex Days Count In One SQL [message #293244 is a reply to message #293170] Fri, 11 January 2008 05:49 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

As long as there is no test case and response from you ,


Here could be One way of doing the same .

1. Select a Full list of Period range for BASE_PERIOD for all primary_key

2. Select a Full list of Period range for EXCLUDE_PERIOD for all corrsponding Foreign_key

3. Do <First Query > Minus <Secod query>

This is homework for you Smile

Thumbs Up
Rajuvan.
Re: Complex Days Count In One SQL [message #293247 is a reply to message #293244] Fri, 11 January 2008 06:18 Go to previous messageGo to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
Maybe I post an example to clarify what I want:

base_period
-----------
primary_key  from_date   to_date
-----------  ----------  -----------
AAA          1 Jan 2008  30 Jan 2008

exclude_period
--------------
foreign_key   from_date   to_date
-----------  -----------  -----------
AAA           5 Jan 2008  13 Jan 2008
AAA          10 Jan 2008  20 Jan 2008
AAA          17 Jan 2008  25 Jan 2008
(no other rows with foreign_key = 'AAA' in exclude_period.


Suppose I only interest in period with primary key 'AAA'.
I want to count the number of days in period 'AAA' in
base_period, excluding those days in exclude_period in just
one single SQL. That is, a single SQL that will give the
results of 9 (9 days in base_period, excluding all those
period in exclude_period, without double counting).

Thanks a lot.
Re: Complex Days Count In One SQL [message #293251 is a reply to message #293170] Fri, 11 January 2008 06:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:

1. Select a Full list of Period range for BASE_PERIOD for all primary_key

2. Select a Full list of Period range for EXCLUDE_PERIOD for all corrsponding Foreign_key

3. Do <First Query > Minus <Secod query>


Did you try as above ?
If so what result are you getting ?

EXISTS clause also a good option to get the result.

Thumbs Up
Rajuvan.
Re: Complex Days Count In One SQL [message #293254 is a reply to message #293247] Fri, 11 January 2008 07:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand in:
Quote:

Post a test case: create table and insert statements.


Regards
Michel
Re: Complex Days Count In One SQL [message #293263 is a reply to message #293247] Fri, 11 January 2008 08:43 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
tmlaio wrote on Fri, 11 January 2008 06:18


Suppose I only interest in period with primary key 'AAA'.
I want to count the number of days in period 'AAA' in
base_period, excluding those days in exclude_period in just
one single SQL. That is, a single SQL that will give the
results of 9 (9 days in base_period, excluding all those
period in exclude_period, without double counting).



Yesterday I wrote a small note on how to deal with overlapping intervals: http://volder-notes.blogspot.com/2008/01/grouping-overlapping-intervals.html.

So in your case:
SQL> with base_period as (select 'AAA' primary_key, date '2008-01-01' from_date, date '2008-01-30' to_date from dual),
  2       exclude_period as (select 'AAA' foreign_key, date '2008-01-05' from_date, date '2008-01-13' to_date from dual union all
  3                          select 'AAA' foreign_key, date '2008-01-10' from_date, date '2008-01-20' to_date from dual union all
  4                          select 'AAA' foreign_key, date '2008-01-17' from_date, date '2008-01-25' to_date from dual)
  5          --
  6  select foreign_key, bp.to_date-bp.from_date - sum(end_d-beg_d) day_numb from (
  7  select foreign_key, min(from_date) beg_d, max(to_date) end_d
  8       from (select t1.*,
  9                    sum(start_of_group) over(partition by foreign_key order by from_date, to_date) gr
 10               from (select t.*,
 11                            case
 12                              when from_date >
 13                                   nvl(max(to_date)
 14                                       over(partition by foreign_key order by from_date,to_date
 15                                            rows between unbounded preceding and 1 preceding),
 16                                       from_date-1)
 17                              then 1
 18                              else 0
 19                            end start_of_group
 20                       from exclude_period  t) t1)
 21      group by foreign_key, gr),base_period bp
 22      where foreign_key=primary_key
 23      group by foreign_key
 24  /

FOREIGN_KEY   DAY_NUMB
----------- ----------
AAA                  9

SQL> 

Re: Complex Days Count In One SQL [message #293385 is a reply to message #293263] Fri, 11 January 2008 23:46 Go to previous messageGo to next message
tmlaio
Messages: 6
Registered: September 2006
Junior Member
Volder, thank you very much, but I cannot do that with Oracle 8.
Re: Complex Days Count In One SQL [message #293387 is a reply to message #293385] Sat, 12 January 2008 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 11 January 2008 09:09

Post a test case: create table and insert statements.

Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel


Re: Complex Days Count In One SQL [message #293410 is a reply to message #293385] Sat, 12 January 2008 05:44 Go to previous message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
tmlaio wrote on Fri, 11 January 2008 23:46

Volder, thank you very much, but I cannot do that with Oracle 8.

what exactly can't you do?
is there an error an oracle throws you? what is the error?
As Michel said post 4 digits of your version.
Previous Topic: Group together data from multiple columns
Next Topic: IF..THEN statements
Goto Forum:
  


Current Time: Sat Dec 03 20:22:11 CST 2016

Total time taken to generate the page: 0.12048 seconds