Solved

SQL Where Clause Help with AND / OR

Posted on 2013-10-27
10
292 Views
Last Modified: 2013-10-27
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

0
Comment
Question by:Newbi22
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39603994
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

0
 

Author Comment

by:Newbi22
ID: 39604005
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

0
 

Author Comment

by:Newbi22
ID: 39604010
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

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 70

Expert Comment

by:Qlemo
ID: 39604013
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.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 39604015
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'))
0
 

Author Comment

by:Newbi22
ID: 39604077
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

0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39604093
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.
0
 

Author Comment

by:Newbi22
ID: 39604117
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
0
 

Author Comment

by:Newbi22
ID: 39604152
Hello....

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

Qlemo was correct in the change. I got the 326....
0
 

Author Closing Comment

by:Newbi22
ID: 39604154
THANK you !!! I need this one..... NOw I get to move on with the project. Trust me, I'll have more post.
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question