Solved

SQL Where Clause Help

Posted on 2013-10-25
3
210 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:Newbi22
3 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 300 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 200 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:Newbi22
ID: 39634465
thnka for the assistance. I got it now.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

733 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