This content has been marked as final. Show 6 replies
SUM(CASE WHEN "Pending Claim - Suspense"."Suspense Date" <= TODAY'S DATE THEN 1 ELSE 0 END)1 person found this helpful
I'm not clear what you want to count/sum; the below is counting "Pending Claim - Suspense"."Suspense Date"
You may modify as needed.
for the column "Pending Claim - Suspense"."Suspense Date" Filter Icon-> Add button click on SQL expression
use Less than operator and current_date
This would filter out the current date.
now pull another column for "Pending Claim - Suspense"."Suspense Date"->fx use count9"Pending Claim - Suspense"."Suspense Date")
You get the count of that column as of Yesterday.
If helps mark
Okay, I see now. I believe that solved it. I set the less than date to current_date. So tomororw it will automatically read everything less than 3/12/2013?
Next question along the same lines. How can I count everything that will be due within the next 7 days?
This would be always less than current date. No worries :)
For other question I'm not clear about due date? if you have it as another column that make life easy, just substract using datetimediff and check for <=7
sum(case when datatimediff(sql_tsi_day,suspendDate,dueDate)<=7 then 1 else 0 end)
if helps mark
Let me clarify some more. The "Due Date" can be in the past or in the future. So for example, I want to capture everything that is coming due this week, March 11 - 17.
I need to be able to count or SUM this number, I don't need it next to every individual record. When it's all done, I will have 3 Columns: # Pending, # Past Due, and # Due within 7 days.
I'm trying to find the # Due within 7 days. Hope this helps!
As I said you have to go with timestampdiff function and then validate for 7.
Its very hard to tell with guess
I would do something like below;
TIMESTAMPDIFF(SQL_TSI_DAY, DueDate, MAX(Time.Date BY Week))
mark if helps
Sorry for the delayed response! I need to play around more with this expression as I don't follow the syntax of it at all.