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