I have two tables: Claim Headers, and Referrals, Referrals has a many-to-one releationship with the Headers, keys are DealerID and Claim Number.
I need to calculate Total No. of Referrals / Total No. Claims.
The problem with the script below is that if a claim has more than one rferral, the header gets counted more than once - I could make a second pass of the table and count distinct claim numbers, but is there a better way?
ClaimStats2T:
LOAD
DealerID, // key
[Claim Number], // key
KPIYear,
KPIMonth,
CountClaims // is always 1, used to sum
Resident ClaimFacts
;
Left Join
LOAD
DealerID, // key
[Claim Number], //key
ReferredDate,
1 as ClaimReferred
;
SQL
...
Regards,
Marty.
