Solved

SQL Where Clause Help with AND / OR

Posted on 2013-10-27
10
272 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
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
 
LVL 68

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 68

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now