SQL Where Clause Help with AND / OR

I have the following code.  In the Where Clause I need help adding the following.....

The Policy Status is NULL OR Active OR Renewed OR Rewritten....

It needs to be both the Policy Rep's initials AND the above status.

The report is suppose to count unique customers, it is working except for the fact that if a customer has a cancelled policy they are still showing up.



SELECT [CDE Book of Business].[Policy Rep] AS Expr1, Count([CDE Book of Business].[Cust No]) AS count_distinct_clients
FROM (SELECT DISTINCT
[CDE Book of Business].[Policy Rep],
[CDE Book of Business].[Cust No]
FROM [CDE Book of Business]
)  AS [CDE Book of Business]
WHERE ((([CDE Book of Business].[Policy Rep])='AK')) OR ((([CDE Book of Business].[Policy Rep])='KD')) OR ((([CDE Book of Business].[Policy Rep])='GP')) OR ((([CDE Book of Business].[Policy Rep])='CC')) OR ((([CDE Book of Business].[Policy Rep])='SA')) OR ((([CDE Book of Business].[Policy Rep])='SP'))
GROUP BY [CDE Book of Business].[Policy Rep];

Open in new window

Michael FranzCFOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
Add the AND outside of the OR chain. Parentheses will be a good idea just to reinforce operator precendence (as AND binds tighter than OR).

...

WHERE ((([CDE Book of Business].[Policy Rep])='AK')) OR ((([CDE Book of Business].[Policy Rep])='KD')) OR ((([CDE Book of Business].[Policy Rep])='GP')) OR ((([CDE Book of Business].[Policy Rep])='CC')) OR ((([CDE Book of Business].[Policy Rep])='SA')) OR ((([CDE Book of Business].[Policy Rep])='SP'))

AND

((([CDE Book of Business].[Policy Status]) IS NULL)) OR (([CDE Book of Business].[Policy Status])='Active')) OR (([CDE Book of Business].[Policy Status])='Renewed')) OR (([CDE Book of Business].[Policy Status])='Rewritten')))

...

Open in new window

Michael FranzCFOAuthor Commented:
After playing with the few ( )... I got past that and now have he following error....


The specified field '[CDE Book of Business].[Policy Status]' could refer to more than one table listed in the FROM clause of your SQL statement.:

Below is the new code with the fixed paran's



SELECT [CDE Book of Business].[Policy Rep] AS Expr1, Count([CDE Book of Business].[Cust No]) AS count_distinct_clients
FROM (SELECT DISTINCT
[CDE Book of Business].[Policy Rep],
[CDE Book of Business].[Cust No]
FROM [CDE Book of Business]
)  AS [CDE Book of Business]
WHERE ((([CDE Book of Business].[Policy Rep])='AK')) OR ((([CDE Book of Business].[Policy Rep])='KD')) OR ((([CDE Book of Business].[Policy Rep])='GP')) OR ((([CDE Book of Business].[Policy Rep])='CC')) OR ((([CDE Book of Business].[Policy Rep])='SA')) OR ((([CDE Book of Business].[Policy Rep])='SP'))
AND
((([CDE Book of Business].[Policy Status]) IS NULL)) OR ((([CDE Book of Business].[Policy Status])='Active')) OR ((([CDE Book of Business].[Policy Status])='Renewed')) OR ((([CDE Book of Business].[Policy Status])='Rewritten'))
GROUP BY [CDE Book of Business].[Policy Rep];

Open in new window

Michael FranzCFOAuthor Commented:
Is this the change that needed to be made????

SELECT [CDE Book of Business].[Policy Rep] AS Expr1, Count([CDE Book of Business].[Cust No]) AS count_distinct_clients
FROM (SELECT DISTINCT
[CDE Book of Business].[Policy Rep],
[CDE Book of Business].[Cust No],



                    [CDE Book of Business].[Policy Status]





FROM [CDE Book of Business]
)  AS [CDE Book of Business]
WHERE ((([CDE Book of Business].[Policy Rep])='AK')) OR ((([CDE Book of Business].[Policy Rep])='KD')) OR ((([CDE Book of Business].[Policy Rep])='GP')) OR ((([CDE Book of Business].[Policy Rep])='CC')) OR ((([CDE Book of Business].[Policy Rep])='SA')) OR ((([CDE Book of Business].[Policy Rep])='SP'))
AND
((([CDE Book of Business].[Policy Status]) IS NULL)) OR ((([CDE Book of Business].[Policy Status])='Active')) OR ((([CDE Book of Business].[Policy Status])='Renewed')) OR ((([CDE Book of Business].[Policy Status])='Rewritten'))
GROUP BY [CDE Book of Business].[Policy Rep];

Open in new window

10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Explicit parens are ok as long as you do not have more than three in sequence, but above is too much. What you have is what a SQL wizard generates, and it needs cleanup. Also, you can simplify asking a single DB field against several alternatives: use IN.
SELECT [Policy Rep], Count([Cust No]) AS count_distinct_clients
FROM (
  SELECT DISTINCT
    [Policy Rep],
    [Cust No],
    [Policy Status]
  FROM [CDE Book of Business]
  )  AS [CDE Book of Business]
WHERE 
[Policy Rep] IN ('AK', 'KD', 'GP', CC', 'SA', 'SP')
AND
([Policy Status] is NULL 
 OR 
 [Policy Status] in ('Renewed', 'Rewritten' /* and so on*/)
)
GROUP BY [Policy Rep];

Open in new window

There are also two things to note: Using DISTINCT is usually bad design, and there might be a better way to restrict the result set to be unique.
And you should use short alias names for your tables, to improve readability. Having all those [CDE Book of Business] lines will get confusing if you have many similar named tables in your query. And also note that you do not need the table name at all if you are selecting a single table.
Hamed NasrRetired IT ProfessionalCommented:
try:

WHERE ([CDE Book of Business].[Policy Rep] IN ('AK', 'KD', 'GP', 'CC', 'SA', 'SP'))
           AND
          (Nz([CDE Book of Business].[Policy Status], 'nullValue') IN ('nullValue', 'Active', 'Renewed', 'Rewritten'))
Michael FranzCFOAuthor Commented:
Hello,

Here is the current code. I am still not getting the right number. The unique number is 326. I parsed it out in excel. I am getting 354. How do I un-group this so I can see the 354 customers. I want to compare the list.

SELECT [Policy Rep], Count([Cust No]) AS count_distinct_clients
FROM (
  SELECT DISTINCT
    [Policy Rep],
    [Cust No],
    [Policy Status]
  FROM [CDE Book of Business]
  )  AS [CDE Book of Business]
WHERE 
[Policy Rep] IN ('AK')
AND
([Policy Status] is NULL 
 OR 
 [Policy Status] in ('Renewed', 'Rewritten' ,'Active')
)
GROUP BY [Policy Rep];
                                        

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Looks like a Rep/Customer set can have more than one status then. You can eliminate that e.g. with:
SELECT [Policy Rep], Count([Cust No]) AS count_distinct_clients
FROM (
  SELECT DISTINCT
    [Policy Rep],
    [Cust No]
  FROM [CDE Book of Business]
  WHERE 
    [Policy Rep] IN ('AK')
    AND
    ([Policy Status] is NULL 
     OR 
    [Policy Status] in ('Renewed', 'Rewritten' ,'Active')
)

  )  AS [CDE Book of Business]
GROUP BY [Policy Rep];

Open in new window

The condition is moved into the inner select, and the status in not part of the "distinct" set, so you should only get a representative once per customer.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FranzCFOAuthor Commented:
I am still getting what I to believe is the wrong number. I included an excel file with the data extract. There are comments and notes in that explain more. I believe the Unique count to be 326, but still getting 354.

I am open to suggestions if there is a better way.

Please forgive me code. I am a accountant trying to write a sql report.
Expert-Exchange-Unique.xlsx
Michael FranzCFOAuthor Commented:
Hello....

Wait... Hold the bus..... I had one thing copied incorrectly.....

Qlemo was correct in the change. I got the 326....
Michael FranzCFOAuthor Commented:
THANK you !!! I need this one..... NOw I get to move on with the project. Trust me, I'll have more post.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.