More efficient formula for sum of counts

[dleewms]

Is there a more efficient way to write the following code? Basically, I am testing four conditions. Then I want to sum how many of those four conditions are true. Then I'm assigning them values A to E (0 to 4). Once an A to E is returned for each record, I am grouping by this value. Just want to know if there is a better way to write this for my own personal growth.

Thanks!

whilereadingrecords;

numbervar collectionGradeCount:=0;
numbervar ct_LPYMTAMT :=0;
numbervar ct_LASTPYDT :=0;
numbervar ct_LSTTRXDT :=0;
numbervar ct_FRSTINDT :=0;

if {AR_open_aging.LPYMTAMT} = 0
then ct_LPYMTAMT := 1;

if {AR_open_aging.LASTPYDT} <CurrentDate-60
then ct_LASTPYDT := 1;

if {AR_open_aging.LSTTRXDT} <CurrentDate-60
then ct_LSTTRXDT := 1;

if {AR_open_aging.FRSTINDT} >CurrentDate-90
then ct_FRSTINDT := 1;

collectionGradeCount:= ct_LPYMTAMT+ ct_LASTPYDT+ct_LSTTRXDT+ct_FRSTINDT;

select collectionGradeCount
case 1 : "B"
case 2: "C"
case 3: "D"
case 4: "E"
default: "A";

 

[briangriffin]

Your formula is fine, I don't know that I'd change it, but you could use a SQL Expression. It will be evaluated on the server so there will be a performance increase, although it may not be noticeable.

//{%YourSQLExpression}
case when {AR_open_aging.LPYMTAMT} = 0 then 1 else 0 end +
case when {AR_open_aging.LASTPYDT} < getdate() - 60 then 1 else 0 end +
case when {AR_open_aging.LSTTRXDT} < getdate() - 60 then 1 else 0 end +
case when {AR_open_aging.FRSTINDT} > getdate() - 90 then 1 else 0 end

Then group on this formula:

select {%YourSQLExpression}

case 0: "A"
case 1: "B"
case 2: "C"
case 3: "D"
case 4: "E"

You'll have to modify the SQL Expression to use sysdate or whatever the currentdate keyword is for your database.