# 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 SUM give all the times wrong numbers Where is wrong value - in SUM field or in partial fields? Try to replace BETWEEN with 2 imparities (first strong and second not). Jun 5, 2018 at 13:01
• Show us some sample data and results. What are the values in `diff` column? Jun 5, 2018 at 13:08
• Necessary link: What do `BETWEEN` and the devil have in common? The blog post may be for SQL Server but the problem is very likely caused by the devil (eh, `BETWEEN` ;) Jun 5, 2018 at 13:41
• Actually diff is constant. So we can ignore it. Should mention it. Jun 6, 2018 at 5:14

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
``````

The numbers are correct. Your assumption of what the values should be is not correct.

# TO_CHAR

You are doing `DATE` comparison. Drop the `TO_CHAR()` calls.

It's not the cause of your issue, but it is a bug in your code.

When doing `DATE` mathematics, make sure everything stays as a `DATE` data type.

# TRUNC( sysdate,'DAY')

`TRUNC(SYSDATE,'DAY')` will truncate to the beginning of the week.

Its Wednsday for me and my local's "begining of the week" is a sunday.

`SUM` should be 4 more than 365. Tomorrow, it should be 5 more.

Wendsday's Result

``````       SUM    SIX_AGO   FIVE_AGO   FOUR_AGO  THREE_AGO    TWO_AGO    ONE_AGO     NO_AGO
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
369        329          6          6          6          6          6          1
``````

SQL to show difference between `TRUNC('DD')` and `TRUNC('DAY')`

``````select
level -1 day_diff, sysdate now
,trunc(sysdate + (level -1)) trunc_default
,trunc(sysdate + (level -1), 'DD') trunc_default
,trunc(sysdate + (level-1), 'DAY') trunc_day
from dual
connect by level <= 10;
``````

Results in

``````  DAY_DIFF NOW       TRUNC_DEF TRUNC_DEF TRUNC_DAY
---------- --------- --------- --------- ---------
0 06-JUN-18 06-JUN-18 06-JUN-18 03-JUN-18
1 06-JUN-18 07-JUN-18 07-JUN-18 03-JUN-18
2 06-JUN-18 08-JUN-18 08-JUN-18 03-JUN-18
3 06-JUN-18 09-JUN-18 09-JUN-18 03-JUN-18
4 06-JUN-18 10-JUN-18 10-JUN-18 10-JUN-18
5 06-JUN-18 11-JUN-18 11-JUN-18 10-JUN-18
6 06-JUN-18 12-JUN-18 12-JUN-18 10-JUN-18
7 06-JUN-18 13-JUN-18 13-JUN-18 10-JUN-18
8 06-JUN-18 14-JUN-18 14-JUN-18 10-JUN-18
9 06-JUN-18 15-JUN-18 15-JUN-18 10-JUN-18
``````

# TOTAL DAYS IN A WEEK

you are doing `BETWEEN TRUNC('DAY') - 7 AND TRUNC('DAY') - 1`

That means you can only pickup dates between `Sunday 00:00` and `Saturday 00:00`

Your date columns include a time component. `Saturday 08:58` is outside your search criteria. As such, the correct answer for the original SQL is `6`. This is what you posted for the answer.

If you want to include `Saturday 08:58` in your search criteria, you'll need to `TRUNC('DD')` your `MY_DATE_COL` first.

``````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 trunc(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_fixed,
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 trunc(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_FIXED
FROM TEST;
``````

Results in

``````       SUM    SIX_AGO SIX_AGO_FIXED   FIVE_AGO FIVE_AGO_FIXED
---------- ---------- ------------- ---------- --------------
369        329           330          6              7
``````