3 Replies Latest reply: Dec 2, 2010 5:40 AM by kellettb RSS

    Help with possibly looping within an expression

       


      COUNT(DISTINCT( If (IsNull(CLOSURE_CODE_ID)=0 AND OWNINGLOCATION='1ST' AND SUM(IF(LOCATION='1ST', 0, ENQUIRY))=0, ENQUIRY)))




      In the above expression I need to count all records that have a value for OwningLocation as "1ST" and also that every record in a related table for the same Enquiry has a value for Location as "1ST" also.

      The second table can concievably have any number of records relating to the one Enquiry. So i figured doing a sum of all those records that dont equal "1ST" would be one way to loop round them all.

      For Example The tables (simplified) could look like this:

      Table A

       

      ENQUIRYOWNINGLOCATION
      121
      1ST
      122
      1ST
      123
      SER
      124
      2ND
      125
      1ST


      Table B

       

      ENQUIRYLOCATIONSequence No
      121
      1ST
      0
      121
      1ST
      1
      121
      1ST
      2
      121
      1ST
      3
      122
      1ST
      0
      122
      2ND
      1
      123
      SER
      0
      124
      1ST
      0
      124
      2ND
      1
      124
      2ND
      2
      125
      1ST
      0
      125
      1ST
      1

       

      So From Table A all i want to count is 121 and 125 as these are the only two enquiries that have "1ST" as the location in every record on the tables. All the other enquiries have moved to a different location at some point.

       

      I am stumped.
      And I am sure everyone reading this is also. Clear as mud ?!