Hello All,

I am trying to create SQL Expressions in my container to categorize AP Open Invoices into aging buckets.  I am successful in getting the Days Outstanding by using the following SQL Expression:

CAST(GETDATE()-"AP_OpenInvoice"."InvoiceDate" AS int)

From here, I would like to do something like this, using 0-29 days as an example:

CASE WHEN CAST(GETDATE()-"AP_OpenInvoice"."InvoiceDate" AS int) < 30 THEN "AP_OpenInvoice"."Balance" ELSE 0

Sage Intelligence errors on the above - Why? What do I have wrong?

Thanks in Advance!


Views: 107

Reply to This

Replies to This Discussion


Thanks for the post, our SQL team is investigating the above issue, we will be in touch with you with our developments.

Please note this email: Peter.Scully@sage.com for any further followups on this issue.

Thank you


Hi Karl.

What Sage product is this for?

Hi Peter!

This is for Sage 100.


Hi Karl.

When subtracting dates it's best to use the DATEDIFF function.


If this doesn't help, let me know what Sage product this is for and I'll take a further look.

Thank you.  I am familiar with the datediff function, however this really doesn't help me with my case SQL expression.  I am using Sage 100.


You are missing the function END at the end of your script, it shoud read

CASE WHEN CAST(GETDATE()-"AP_OpenInvoice"."InvoiceDate" AS int) < 30 THEN "AP_OpenInvoice"."Balance" ELSE 0 END

Thanks, I'll try that.


Thanks for the response.  Your suggestion worked.  This is probably not the most elegant case statement, but here is what I did for the 30-60 day bucket:

CASE when CAST(GETDATE()-"AP_OpenInvoice"."InvoiceDate" AS int) >29
and CAST(GETDATE()-"AP_OpenInvoice"."InvoiceDate" AS int)<60
THEN "AP_OpenInvoice"."Balance" ELSE 0 END


The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2018   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service