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

x
?
Solved

SQL Where Clause Help

Posted on 2013-10-25
3
Medium Priority
?
214 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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In this article, we’ll look at how to deploy ProxySQL.
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

916 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