Skip to main content

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

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

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)

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