none
Omitting a record if its related column information does not match SQL2008

    Question

  • So Im working on a project where there is a column called claimnbr it then can have multiple disputes within it (1-X). The report is to pull any claims that have ONLY bank of america withdrawals. The problem im running into is, some claimnbrs have lets say 3 disputes and 2 have bank of america withdrawal and one pnc bank or any other bank for that matter and is still returning that claimnbr. Im new to sql and im having a tremendously hard time getting it to work.  The other information is housed in anther table where the fraud type is cardsecure or counterfiet or counterfeit <-spelling error lol, and the claimtypcd can be 'atmwp' (these claims are only bank of america but they are non fraud) or 5-6 others so i just want it to see the claim and search its disputes to see if the are all bank of america if not to not select it.

    Current code is as follows:

    USE

    CLAIM GO

    SELECT

    A.ClaimNbr

    FROM

    dbo.CCMSClaimCore A

    LEFT JOIN dbo.CCMSTransactionCore B

    ON A.ClaimNbr = B.ClaimNbr

    WHERE

    B.merchantNm like 'BKOFAMERICA%WITHDR%'

    AND A.Frdtyp IN('Counterfiet', 'Counterfeit', 'CardSecure')

    OR A.ClaimTypCd = 'ATMWP'

    --GROUP BY A.ClaimNbr

    Thursday, March 28, 2013 11:59 PM

Answers

  • Hi Bacon_Strips,

    If I understand correctly, regarding the records example you posted above, you want the dataset query to return the Pnc related data but don’t want it to be displayed in the particular report.

    To do this on the report level, we can hide the particular values conditionally. Please use the following steps:

    1. Right click on the details group from the Row Groups pane, and open Group Properties window.
    2. Click the Visibility tab, select Show or hide based on an expression, and set the expression to:
      =IIF(Fields!MerchantNm.Value like "BK*withdr*",true, false)

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, April 01, 2013 9:55 AM
    Moderator

All replies

  • To be More specific take this data: this claim i want
    ClaimNbr                     DisputeNbr             MerchantNm

    111111111111           125484265987      BKofAmerica 07/13 Withdrawal

    111111111111           125484265988      BKofAmerica 07/13 Withdrawal
    111111111111           125484265989      BkofAmerica 07/13 Withdrawal

    This one i do not:
    222222222222          123456278912    BKofamerica 09/15 withdrawal
    222222222222          123456278917   Bkofamerica 09/15 withdrawal
    222222222222          123456278922    Pnc 09/15 withdrawal  


    Friday, March 29, 2013 12:40 AM
  • Hi Bacon_Strips,

    If I understand correctly, regarding the records example you posted above, you want the dataset query to return the Pnc related data but don’t want it to be displayed in the particular report.

    To do this on the report level, we can hide the particular values conditionally. Please use the following steps:

    1. Right click on the details group from the Row Groups pane, and open Group Properties window.
    2. Click the Visibility tab, select Show or hide based on an expression, and set the expression to:
      =IIF(Fields!MerchantNm.Value like "BK*withdr*",true, false)

    Regards,


    Mike Yin
    TechNet Community Support

    Monday, April 01, 2013 9:55 AM
    Moderator