Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Where Clause Help with AND / OR

Posted on 2013-10-27
10
Medium Priority
?
332 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
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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 …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

926 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