?
Solved

SQL Where Clause Help

Posted on 2013-10-25
3
Medium Priority
?
212 Views
Last Modified: 2013-11-08
I have the following code and I do not know how to modify it for the following..... I need to add a where clause for the following.

Where Referral_Source is NOT NULL OR BLANK/EMPTY.




SELECT NewBusinessHeader.Exec_Name, NewBusinessHeader.Client_Last_Name, NewBusinessLineItem.EffectiveDate, NewBusinessLineItem.Carrier, NewBusinessLineItem.LineOfBusiness, NewBusinessLineItem.SoldPremium, NewBusinessLineItem.Sold, NewBusinessHeader.Referral_Source,NewBusinessHeader.Branch,NewBusinessHeader.Dept,NewBusinessHeader.Customer_Number as Agency_Rev,NewBusinessHeader.Process_Complete_Date,NewBusinessHeader.Group_Submission_Number

FROM NewBusinessHeader INNER JOIN NewBusinessLineItem ON NewBusinessHeader.NBHeaderID = NewBusinessLineItem.NBHeaderID

WHERE ((NewBusinessLineItem.Sold='Yes')) AND (([NewBusinessHeader].[Process_Complete_Date])>=<<Enter the From Date[Date]>> AND [NewBusinessHeader].[Process_Complete_Date]<= <<Enter the To Date[Date]>>) AND (((NewBusinessHeader.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))

UNION ALL

SELECT Submissions.Exec_Name, Submissions.Client_Last_Name,Submissions.EffectiveDate,Submissions.Carrier, Submissions.LineOfBusiness,Submissions.SoldPremium,Submissions.Sold,Submissions.Referral_Source,Submissions.Branch,Submissions.Dept,Submissions.Agency_Rev,Submissions.Process_Complete_Date,Submissions.Group_Submission_Number

FROM Submissions

WHERE ((Submissions.Sold='Yes')) AND Process_Complete_Date >= <<Enter the From Date[Date]>> AND Process_Complete_Date<= <<Enter the To Date[Date]>> AND (((Submissions.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))

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
3 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 1200 total points
ID: 39600405
try with this

SELECT * FROM
(
SELECT NewBusinessHeader.Exec_Name, NewBusinessHeader.Client_Last_Name, NewBusinessLineItem.EffectiveDate, NewBusinessLineItem.Carrier, NewBusinessLineItem.LineOfBusiness, NewBusinessLineItem.SoldPremium, NewBusinessLineItem.Sold, NewBusinessHeader.Referral_Source,NewBusinessHeader.Branch,NewBusinessHeader.Dept,NewBusinessHeader.Customer_Number as Agency_Rev,NewBusinessHeader.Process_Complete_Date,NewBusinessHeader.Group_Submission_Number

FROM NewBusinessHeader INNER JOIN NewBusinessLineItem ON NewBusinessHeader.NBHeaderID = NewBusinessLineItem.NBHeaderID

WHERE ((NewBusinessLineItem.Sold='Yes')) AND (([NewBusinessHeader].[Process_Complete_Date])>=<<Enter the From Date[Date]>> AND [NewBusinessHeader].[Process_Complete_Date]<= <<Enter the To Date[Date]>>) AND (((NewBusinessHeader.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))

UNION ALL

SELECT Submissions.Exec_Name, Submissions.Client_Last_Name,Submissions.EffectiveDate,Submissions.Carrier, Submissions.LineOfBusiness,Submissions.SoldPremium,Submissions.Sold,Submissions.Referral_Source,Submissions.Branch,Submissions.Dept,Submissions.Agency_Rev,Submissions.Process_Complete_Date,Submissions.Group_Submission_Number

FROM Submissions

WHERE ((Submissions.Sold='Yes')) AND Process_Complete_Date >= <<Enter the From Date[Date]>> AND Process_Complete_Date<= <<Enter the To Date[Date]>> AND (((Submissions.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))
)AS A
WHERE Referral_Source IS NOT NULL OR Referral_Source = ''

Open in new window

0
 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 800 total points
ID: 39601039
>> Where Referral_Source is NOT NULL OR BLANK/EMPTY.

For this, "WHERE Referral_Source IS NOT NULL" is sufficient

>> Where Referral_Source is NOT NULL OR NOT BLANK/EMPTY.

For this "WHERE Referral_Source <> ''" is needed.

Modified Brichsoft solution. for this.
SELECT * FROM
(
SELECT NewBusinessHeader.Exec_Name, NewBusinessHeader.Client_Last_Name, NewBusinessLineItem.EffectiveDate, NewBusinessLineItem.Carrier, NewBusinessLineItem.LineOfBusiness, NewBusinessLineItem.SoldPremium, NewBusinessLineItem.Sold, NewBusinessHeader.Referral_Source,NewBusinessHeader.Branch,NewBusinessHeader.Dept,NewBusinessHeader.Customer_Number as Agency_Rev,NewBusinessHeader.Process_Complete_Date,NewBusinessHeader.Group_Submission_Number

FROM NewBusinessHeader INNER JOIN NewBusinessLineItem ON NewBusinessHeader.NBHeaderID = NewBusinessLineItem.NBHeaderID

WHERE ((NewBusinessLineItem.Sold='Yes')) AND (([NewBusinessHeader].[Process_Complete_Date])>=<<Enter the From Date[Date]>> AND [NewBusinessHeader].[Process_Complete_Date]<= <<Enter the To Date[Date]>>) AND (((NewBusinessHeader.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))

UNION ALL

SELECT Submissions.Exec_Name, Submissions.Client_Last_Name,Submissions.EffectiveDate,Submissions.Carrier, Submissions.LineOfBusiness,Submissions.SoldPremium,Submissions.Sold,Submissions.Referral_Source,Submissions.Branch,Submissions.Dept,Submissions.Agency_Rev,Submissions.Process_Complete_Date,Submissions.Group_Submission_Number

FROM Submissions

WHERE ((Submissions.Sold='Yes')) AND Process_Complete_Date >= <<Enter the From Date[Date]>> AND Process_Complete_Date<= <<Enter the To Date[Date]>> AND (((Submissions.Dept) In (<<Enter Personal Lines, Commercial Lines, Benefit lines (seperated by a comma for mulitple) [Text]+>>)))
)AS A
WHERE Referral_Source <> ''

Open in new window

0
 

Author Closing Comment

by:Michael Franz
ID: 39634465
thnka for the assistance. I got it now.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

771 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