CODEWITHSUNDEEP
sqloracle-database
user348173
user348173

Reputation: 9278

check date ranges with other date ranges

I have the table Distractionswith the following columns:
id startTime endTime(possible null)

Also, I have two parameters, it's period. pstart and pend.
I have to find all distractions for the period and count hours.
For example, we have:
Distractions: 

 `id`      `startTime`                   `endTime`  
   1     01.01.2014 00:00             03.01.2014 00:00
   2     25.03.2014 00:00             02.04.2014 00:00
   3     27.03.2014 00:00                   null

The columns contains time, but don't use them.
Period is pstart = 01.01.2014 and pend = 31.03.2014
For example above the result is equal:

the sum is equal 360.

My code:

select 
     sum ((ds."endTime" - ds."startTime")*24) as hoursCount
from "Distractions" ds
--where ds."startTime" >= :pstart and ds."endTime" <= :pend
-- I don't know how to create where condition properly.

Upvotes: 0

Views: 52

Answers (1)

Noel
Noel

Reputation: 10525

You'll have to take care of cases where date ranges are outside the input range and also account for starttime and endtime being null. This where clause should keep only the valid data ranges. I have substituted the null startime with a earliest date and null endtime with a date far in the future.

where coalesce(endtime,date'9999-12-31')   >= :pstart
  and coalesce(starttime,date'0000-01-01') <= :pend

Once you have filtered records, you need to adjust the date values so that anything starting before the input :pstart is moved forward to the :pstart, and anything ending after :pend is moved back to :pend. Subtracting these two should give the value you are looking for. But, there is a catch. Since the time is 00:00:00, when you subtract the dates, it will miss one full day. So, add 1 to it.

SQL Fiddle

Oracle 11g R2 Schema Setup:

create table myt(
id number,
starttime date,
endtime date
);

insert into myt values( 1 ,date'2014-01-01', date'2014-01-03');
insert into myt values( 2 ,date'2014-03-25', date'2014-04-02');
insert into myt values( 3 ,date'2014-03-27', null);
insert into myt values( 4 ,null, date'2013-04-02');
insert into myt values( 5 ,date'2015-03-25', date'2015-04-02');
insert into myt values( 6 ,date'2013-12-25', date'2014-04-09');
insert into myt values( 7 ,date'2013-12-26', date'2014-01-09');

Query 1:

select id,
       case when coalesce(starttime,date'0000-01-01') < date'2014-01-01'
            then date'2014-01-01'
            else starttime
       end adj_starttime,
       case when coalesce(endtime,date'9999-12-31') > date'2014-03-31'
            then date'2014-03-31'
            else endtime
       end adj_endtime,
       (case when coalesce(endtime,date'9999-12-31') > date'2014-03-31'
            then date'2014-03-31'
            else endtime
       end -
       case when coalesce(starttime,date'0000-01-01') < date'2014-01-01'
            then date'2014-01-01'
            else starttime
       end
       + 1) * 24 hoursCount
from myt
where coalesce(endtime,date'9999-12-31') >= date'2014-01-01'
and coalesce(starttime,date'0000-01-01') <= date'2014-03-31'
order by 1

Results:

| ID |                  ADJ_STARTTIME |                    ADJ_ENDTIME | HOURSCOUNT |
|----|--------------------------------|--------------------------------|------------|
|  1 | January, 01 2014 00:00:00+0000 | January, 03 2014 00:00:00+0000 |         72 |
|  2 |   March, 25 2014 00:00:00+0000 |   March, 31 2014 00:00:00+0000 |        168 |
|  3 |   March, 27 2014 00:00:00+0000 |   March, 31 2014 00:00:00+0000 |        120 |
|  6 | January, 01 2014 00:00:00+0000 |   March, 31 2014 00:00:00+0000 |       2160 |
|  7 | January, 01 2014 00:00:00+0000 | January, 09 2014 00:00:00+0000 |        216 |

Upvotes: 2

Related Questions