Oracle BETWEEN and SUM Problem – Troubleshooting Guide

oracle

I have a query below.

The SUM should be the SIX_AGO + FIVE_AGO + FOUR_AGO + etc.

But the SUM give all the times wrong numbers.

Is there anything I am doing wrong?

 SELECT  
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO 
FROM TEST

Best Answer

The question does not contain test data, and some details are not quite clear eg we don't know what the "diff" in the original query stands for. Suppose we have the following test table (assuming that diff is just a constant value):

-- Oracle 12c
create table test
as
select
  sysdate - level as my_date_col
, 1 as diff
from dual
connect by level <= 400 ;

SQL> select * from test fetch first 10 rows only ;
MY_DATE_COL  DIFF  
04-JUN-18    1     
03-JUN-18    1     
02-JUN-18    1     
01-JUN-18    1     
31-MAY-18    1     
30-MAY-18    1     
29-MAY-18    1     
28-MAY-18    1     
27-MAY-18    1     
26-MAY-18    1

Original query

The counts for the "sections" (SIX_AGO, FIVE_AGO etc) do not add up to the expected "SUM".

 SELECT  
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS SUM, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 365 -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 36 - diff) then 1 END) AS SIX_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 35  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 29 -  diff) then 1 END) AS FIVE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 28  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 22 -  diff) then 1 END) AS FOUR_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 21  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 15 -  diff) then 1 END) AS THREE_AGO,
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 14  -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 8  -  diff) then 1 END) AS TWO_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') - 7   -  diff ) AND to_date(TRUNC(sysdate, 'DAY') - 1  -  diff) then 1 END) AS ONE_AGO, 
  COUNT(CASE WHEN MY_DATE_COL BETWEEN to_date(TRUNC(sysdate, 'DAY') -        diff ) AND to_date(TRUNC(sysdate, 'DAY') + 6  -  diff) then 1 END) AS NO_AGO 
FROM TEST ;

-- result
SUM  SIX_AGO  FIVE_AGO  FOUR_AGO  THREE_AGO  TWO_AGO  ONE_AGO  NO_AGO  
367  329      6         6         6          6        6        2

The problem seems to be that the nested functions used in BETWEEN ... AND ... lead to incorrect counts. Thus, I suggest that we rewrite the query in a way that allows us to use integer values for the between conditions. Something like ...

select
  count( case when trunc( my_date_col - sysdate ) between -7   and -1  then 1 end ) section1 
, count( case when trunc( my_date_col - sysdate ) between -14  and -8  then 1 end ) section2 
, count( case when trunc( my_date_col - sysdate ) between -21  and -15 then 1 end ) section3 
, count( case when trunc( my_date_col - sysdate ) between -28  and -22 then 1 end ) section4 
, count( case when trunc( my_date_col - sysdate ) between -35  and -29 then 1 end ) section5 
, count( case when trunc( my_date_col - sysdate ) between -365 and -36 then 1 end ) section6 
, count( case when trunc( my_date_col - sysdate ) between -365 and -1  then 1 end ) overall 
from test ;

-- result
SECTION1  SECTION2  SECTION3  SECTION4  SECTION5  SECTION6  OVERALL  
7         7         7         7         7         330       365

Related Solutions

Struggling with complicated date query

I'll do it in stages to make it easier to understand.

I put your data into a table called EX_DATA - added a primary key as I assume you'll have one and it makes things a little easier:

create table ex_data
(
  contract_id number(18),
  expire_dt date,
  term_notice number(18),
  notice_freq varchar(20),
  notice_months number
);


insert into ex_data values(1,TO_DATE('01-MAR-2005','DD-MON-YYYY'), 60, 'YEARLY', 12);

insert into ex_data values(2,TO_DATE('01-DEC-2007','DD-MON-YYYY'), 30, 'MONTHLY', 1);

insert into ex_data values(3,TO_DATE('28-FEB-2008','DD-MON-YYYY') ,30, 'YEARLY', 12);

The next expiry date from the first given date will be:

select add_months(expire_dt, notice_months) as next_expiry
from ex_data;

Then, it follows that the notice period is a bit of easy date arithmetic too:

select add_months(expire_dt, notice_months) as next_expiry, 
       add_months(expire_dt, notice_months) - term_notice as notice_date
from ex_data;

Using DUAL as a source, generate expiry dates for lots of years:

select expire_dt as first_expiry_date,
       l as expiry_num,
       add_months(expire_dt,l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
order by l;

The next expiration date, and the date they should have been contacted for renegotiation:

select first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as renegotiation_date
from
(
select expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates >sysdate
and all_expiry_dates - term_notice < sysdate 
;

But, that's not what I think you're asking. You want the next renewal date where the contact period hasn't yet been hit, which can be done as follows:

select * from (
select contract_id, first_expiry_date, all_expiry_dates as next_expiry_date, all_expiry_dates - term_notice as notification_date, ROW_NUMBER( ) OVER (PARTITION BY
contract_id ORDER BY all_expiry_dates) r
from
(
select contract_id, expire_dt as first_expiry_date, l as expiry_period_num, term_notice, add_months(expire_dt, l*notice_months) as all_expiry_dates, notice_months
from ( select level l from dual connect by level < 100 ) l, ex_data
)
where all_expiry_dates - term_notice > sysdate 
)
where r=1;

Can probably be done with some simple SYSDATE maths along with the source data, but my brain is a little slow this evening ;)

Comparing values between two tables

Assuming that sql_id has a unique or primary key constraint, you can do this with FULL JOIN (or LEFT JOIN if there are no new "inserts" in table B, or just JOIN if there are no inserts and no deletions):

SELECT 
    COALESCE(a.sql_id,b.sql_id) AS sql_id,
    CASE WHEN a.operation = b.operation THEN 'matched' 
         WHEN a.operation <> b.operation THEN 'value changed'
         WHEN b.operation IS NULL THEN 'a deleted'
         WHEN a.operation IS NULL THEN 'b inserted' 
    END AS status,
    a.operation AS a_operation,
    b.operation AS b_operation
FROM 
  a FULL JOIN b
      ON a.sql_id = b.sql_id ;

Best Answer

Related Solutions

Struggling with complicated date query

Comparing values between two tables

Related Question