1 Reply Latest reply: Aug 13, 2014 5:14 AM by Tresesco B RSS

    Problem counting headers

    Martyn Lloyd

      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.