0

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
4
  • 1
    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).
    – Akina
    Jun 5, 2018 at 13:01
  • 3
    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.
    – Martin P.
    Jun 6, 2018 at 5:14

2 Answers 2

2

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 
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.