CR 9 - Sybase ASA 6.0.2
Having a difficult time finding the correct way to come up with a sum....
group 1 - ServCatDesc
group 2 - AccountNo
group 3 - ActivityTypeDesc
group 4 - ActivityNo
detail - pat.masterno (may or may not be present), activitylength (always present), personid (always present)
Sum for group 2 (AccountNo) should say - ok, look at the detail lines for each activityno (group 4) - include the activitylength in the sum for group 2 for each detail line unless the personid is the same
(if this is of any assistance, in Sybase's Infomaker the expression was sum( activity_activitylength for group 2 distinct activity_activityno, personid ))
eg
Occupational Therapy
1162465
Single Ax
111
detail 1 =masterno = 771, activitylength = 60, personid = 21 - yes
Occupational Therapy
1162465
Single Ax
112
detail 2 =masterno = (null), activitylength = 15, personid = 21 - yes, even though masterno is null
Occupational Therapy
1162465
Single Ax
113
detail 3 =masterno = 772, activitylength = 90, personid = 22 - yes
detail 4 =masterno = 772, activitylength = 90, personid = 23 - yes, personid is NOT the same
Occupational Therapy
1162465
Single Ax
114
detail 5 =masterno = 773, activitylength = 120, personid = 24 - yes
detail 6 =masterno = 774, activitylength = 120, personid = 24 - NO, because the personid AND the activityno are the same as detail 5 (shows up 2x because the masterno is different)
so in this case, the group 2 sum would be 375.
I have tried a myriad of things. The closest I came was creating a formula (@activityno_personid) that added activityno and personid and then used a running total - summary - field - activitylength; evaluate - change of field - @activityno_personid); reset - change of group - accountno - however, instances arose where the result of the formula caused an incorrect duplicate - eg, activityno = 10 + personid = 21 = 31 same result as activityno = 21 + personid = 10...
All help much appreciated. Please let me know if I can provide more info or make things clearer.....
Having a difficult time finding the correct way to come up with a sum....
group 1 - ServCatDesc
group 2 - AccountNo
group 3 - ActivityTypeDesc
group 4 - ActivityNo
detail - pat.masterno (may or may not be present), activitylength (always present), personid (always present)
Sum for group 2 (AccountNo) should say - ok, look at the detail lines for each activityno (group 4) - include the activitylength in the sum for group 2 for each detail line unless the personid is the same
(if this is of any assistance, in Sybase's Infomaker the expression was sum( activity_activitylength for group 2 distinct activity_activityno, personid ))
eg
Occupational Therapy
1162465
Single Ax
111
detail 1 =masterno = 771, activitylength = 60, personid = 21 - yes
Occupational Therapy
1162465
Single Ax
112
detail 2 =masterno = (null), activitylength = 15, personid = 21 - yes, even though masterno is null
Occupational Therapy
1162465
Single Ax
113
detail 3 =masterno = 772, activitylength = 90, personid = 22 - yes
detail 4 =masterno = 772, activitylength = 90, personid = 23 - yes, personid is NOT the same
Occupational Therapy
1162465
Single Ax
114
detail 5 =masterno = 773, activitylength = 120, personid = 24 - yes
detail 6 =masterno = 774, activitylength = 120, personid = 24 - NO, because the personid AND the activityno are the same as detail 5 (shows up 2x because the masterno is different)
so in this case, the group 2 sum would be 375.
I have tried a myriad of things. The closest I came was creating a formula (@activityno_personid) that added activityno and personid and then used a running total - summary - field - activitylength; evaluate - change of field - @activityno_personid); reset - change of group - accountno - however, instances arose where the result of the formula caused an incorrect duplicate - eg, activityno = 10 + personid = 21 = 31 same result as activityno = 21 + personid = 10...
All help much appreciated. Please let me know if I can provide more info or make things clearer.....