Skip to main content
Solved

COMBINING SUM SQL

  • September 21, 2021
  • 3 replies
  • 151 views
R
Sidekick
Forum|alt.badge.img+7

I am trying to see the total records remaining to count subtracting the records that have been received this year. below is the SQL i am using and i get the same number of  records with out excluding the records that were received this year

example 

what i am seeing using my current SQL 21 records 6 received this year column should read 15 records but it is still reading 21 records

 

SUM(CASE WHEN RECEIPT_DATE > to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' ) and LAST_COUNT_DATE > trunc(to_date( '#START_OF_THIS_YEAR#', 'YYYY-MM-DD-HH24:MI:SS' )) + ( 1 - 1/ ( 60*60*24 ) )THEN '0' ELSE '1' END)

 

Thank you for any help

Best answer by Tomas Ruderfelt

Forget the above, I did misunderstand the requirement.

Here is an example which counts

  • Records where receipt_date is last year
  • LAST_COUNT_DATE is not set or earlier than 2021-01-01 23:59:59 (Which is the condition you wanted?)

SUM(CASE WHEN RECEIPT_DATE < TRUNC(SYSDATE,'YEAR') and nvl(LAST_COUNT_DATE, trunc(SYSDATE-370)) < TRUNC(SYSDATE,'YEAR') + ( 1 - 1/ ( 60*60*24 ) )THEN 1 ELSE 0 END)

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

3 replies

T
Superhero (Employee)
Forum|alt.badge.img+19
  • Tomas Ruderfelt
  • Superhero (Employee)
  • 488 replies
  • September 22, 2021

Does all the records have a value in LAST_COUNT_DATE? If not those will always be counted as 1.

T
Superhero (Employee)
Forum|alt.badge.img+19
  • Tomas Ruderfelt
  • Superhero (Employee)
  • 488 replies
  • Answer
  • September 22, 2021

Forget the above, I did misunderstand the requirement.

Here is an example which counts

  • Records where receipt_date is last year
  • LAST_COUNT_DATE is not set or earlier than 2021-01-01 23:59:59 (Which is the condition you wanted?)

SUM(CASE WHEN RECEIPT_DATE < TRUNC(SYSDATE,'YEAR') and nvl(LAST_COUNT_DATE, trunc(SYSDATE-370)) < TRUNC(SYSDATE,'YEAR') + ( 1 - 1/ ( 60*60*24 ) )THEN 1 ELSE 0 END)

R
1 person likes this
  • R
    Ravindu Suriarachchi
R
Sidekick
Forum|alt.badge.img+7
  • Rpauley
  • Author
  • Sidekick
  • 65 replies
  • September 22, 2021

Thomas Ruderfelt

 In some cases the last count date is blank untill the part has been counted

 

thank you for the help seems what you provided will work Thank you again

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings