1

I am trying run below query to get records based on selected agency codes and then if agency codes is 'TCIA' then I wants to select only selected producer code of records. Here is a basic criteria;

I want to retrieve all the records where Agency Codes are '4131612','4300113','4300113','TCIA' IF Agency code = 'TCIA' then I want ONLY records where producer code values IN 'P125XX','P126XX'

I wrote below query but the problem is query is ignoring all the statements after OR clause. For example it retrieving all the records those have agency code as TCIA but ignored other statements such as recordstype, appregion, date etc.

SELECT * FROM [Policy]
WHERE
RECORDTYPE= 'POLICY' AND
POLICYEFFECTIVEDATE > '2017-12-31' AND
POLICYEXPIRATIONDATE > GETDATE() AND
POLICYSTATUS = 'ACTIVE' AND
AGENCYCODE IN ('4131612','4300113','4300113')
OR
AGENCYCODE = 'TCIA' AND 
PRODUCERCODE IN ('P125XX','P126XX')

Any idea how to do this?

1 Answer 1

3

Order of operations. I would recommend always using () when comparing that many conditions. Your 'OR' is messing up since the query is being run as:

SELECT * FROM [Policy] WHERE (RECORDTYPE= 'POLICY' AND POLICYEFFECTIVEDATE > '2017-12-31' AND POLICYEXPIRATIONDATE > GETDATE() AND POLICYSTATUS = 'ACTIVE' AND AGENCYCODE IN ('4131612','4300113','4300113'))

OR

(AGENCYCODE = 'TCIA' AND PRODUCERCODE IN ('P125XX','P126XX'))

Try this:

SELECT * FROM [Policy] WHERE RECORDTYPE= 'POLICY' AND POLICYEFFECTIVEDATE > '2017-12-31' AND POLICYEXPIRATIONDATE > GETDATE() AND POLICYSTATUS = 'ACTIVE' AND

(AGENCYCODE IN ('4131612','4300113','4300113') OR AGENCYCODE = 'TCIA')

AND PRODUCERCODE IN ('P125XX','P126XX')

1
  • Thanks Mike, there is a small changes need to be done in your query. With your query it only brings records where AGENCYCODE = 'TCIA') AND PRODUCERCODE IN ('P125XX','P126XX'). So I made a small change which is; SELECT * FROM [Policy] WHERE RECORDTYPE= 'POLICY' AND POLICYEFFECTIVEDATE > '2017-12-31' AND POLICYEXPIRATIONDATE > GETDATE() AND POLICYSTATUS = 'ACTIVE' AND (AGENCYCODE IN ('4131612','4300113','4300113') OR (AGENCYCODE = 'TCIA') AND PRODUCERCODE IN ('P125XX','P126XX'))
    – Sohail
    Mar 8, 2018 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.