Problem with BETWEEN and SUM 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
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 &lt;= 400 ;

SQL&gt; 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