Results 1 to 3 of 3

Thread: Help with Query

  1. #1
    Join Date
    May 2009
    Posts
    5

    Question Unanswered: Help with Query


    Hi All,

    I have a table (VWTbl). In this table there is an Order_No field and a Claim_type field (amongst others). I want to create a query that only shows order numbers where more than one claim type has been applied. In this instance claim types would be SRC & 4C.


    So a query that outputs the order number only when claim types src and 4c have been both applied to the same order number.

    P0508
    Last edited by p0508; 05-20-09 at 02:27. Reason: try to make the question easier to understand

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How do you determine that more than one claim have been applied for one order? Do you have several Claim column in the table or do you put the different claims (I suppose that SRC and 4C are claim codes), such as SRC, 4C or SRC4C or any other kind of combination? I also suppose (and hope!) that there is only one row per Order_No.

    Could you please supply more explanations about your data model?

    Have a nice day!

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Making some assumptions until more information is posted....

    Your data looks like this (the Claim_type is NOT repeated per Order_No):
    Code:
    Order_No, Claim_type
    1         A
    1         SRC
    1         B
    2         SRC
    2         4C
    2         C
    Then this will get Order_No with both the Claim_types:

    Code:
    SELECT Order_No
    FROM VWTbl
    WHERE Claim_type IN('SRC', '4C')
    GROUP BY Claim_No
    HAVING COUNT(*) >= 2
    If Claim_type IS duplicate within Order_No like this:

    Code:
    Order_No, Claim_type
    1         A
    1         SRC
    1         SRC
    2         SRC
    2         4C
    2         4C
    Then:
    Code:
    SELECT Order_No
    FROM(SELECT DISTINCT Order_no, Claim_type
         FROM VWTbl
         WHERE Claim_type IN('SRC', '4C')
        ) as UNIQ_DATA
    GROUP BY Claim_No
    HAVING COUNT(*) >= 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •