Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Where Clause Help with AND / OR

Posted on 2013-10-27
10
Medium Priority
?
313 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:Michael Franz
[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:Michael Franz
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:Michael Franz
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
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 
LVL 71

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 31

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:Michael Franz
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 71

Accepted Solution

by:
Qlemo earned 2000 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:Michael Franz
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:Michael Franz
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:Michael Franz
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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