Solved

SQL Query - Show clients that do not have a project and that have projects for a specific year

Posted on 2012-03-28
15
307 Views
Last Modified: 2012-03-29
I have two tables.  Clients and CltDue (cltdue contains projects for the client)

I would like to query clients that were made active between two dates and have projects for a specific year.  But... here is the part that I do not know how to accomplish, I need to also show that they do not have a project created for this year as well.

So I will list all of the projects for the clients in say, 2012.  But in the same report I need to know if they do not have a project already created in 2012.  So the client info will show up but the project fields will be blank.

Here is the query for showing projects for 2012.

SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname, CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID) INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
WHERE (((Clients.Maractdate) Between @BeginDate And @EndDate) AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0) AND ((Year([CltDue].[CDDue]))=@Year20xx));

Open in new window


Thanks!
0
Comment
Question by:ITMcmcpa
  • 7
  • 7
15 Comments
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37776856
Add an IS NULL check to the date
SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname, CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID) INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
WHERE (((Clients.Maractdate) Between @BeginDate And @EndDate) AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0) AND ((Year([CltDue].[CDDue]))=@Year20xx OR cltDue IS NULL));

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37776860
I meant [CltDue].[CDDue] IS NULL, sorry for the typo
0
 
LVL 8

Expert Comment

by:Leo Torres
ID: 37776915
try

SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, 
Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname,
CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID) 
	INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
and (((Clients.Maractdate) Between @BeginDate And @EndDate) 
AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0) AND ((Year([CltDue].[CDDue]))=@Year20xx));

Open in new window

0
 

Author Comment

by:ITMcmcpa
ID: 37777003
Unfortunately neither of these show the clients that do not have a project for the queried year.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37777040
Well, that depends on the structure you have. You can try:
SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname, CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID AND ((Year([CltDue].[CDDue]))=@Year20xx OR [CltDue].[CDDue] IS NULL)) INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
WHERE (((Clients.Maractdate) Between @BeginDate And @EndDate) AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0));

Open in new window

I think I got the correct number of ( and ). But if this doesn't work for you, you'd have to give an example of what happens when a client doesn't have a project for the queried year.
0
 

Author Comment

by:ITMcmcpa
ID: 37777362
What I would like to see:

ID 	Maractdate	Cltnum	CltEng	Engdept	Engdesc	Cltname	Cltoff	Emplname	Empfname	CDDue 	CDID	CDEventDesc	CDDescription	
31365	1/2/2012	101408	TAX	1	Tax Services	1 Test Client	11	Jones	John	1/15/2012	139984	1040	Some Description	< Has Project
31365	1/2/2012	101408	TAX	1	Tax Services	1 Test Client	11	Jones	John	1/20/2012	139985	740	Another Description	< Has Project
58152	3/20/2012	666666	TAX	1	Tax Services	2 Test Client	11	Doe	Peter					< No Project  

Open in new window

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37777441
I understand that. What I mean is, what happens on the CltDue table? Do you want the ones that have no project ever, or just the ones that have no project in 2012?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:ITMcmcpa
ID: 37777450
Just ones that do not have a project in 2012 if that is the year I enter for the parameter.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 37777491
Ok, then this should work:
SELECT Clients.ID, Clients.Maractdate, Clients.Cltnum, Clients.CltEng, Clients.Engdept, Clients.Engdesc, Clients.Cltname, Clients.Cltoff, Employee.Emplname, Employee.Empfname, CltDue.CDDue, CltDue.CDID, CltDue.CDEventDesc, CltDue.CDDescription
FROM (Clients LEFT JOIN CltDue ON Clients.ID = CltDue.CDCltID  AND ((Year([CltDue].[CDDue]))=@Year20xx)) INNER JOIN Employee ON Clients.Engcltptr = Employee.ID
WHERE (((Clients.Maractdate) Between @BeginDate And @EndDate) AND ((Clients.Engstatus)=0) AND ((CltDue.Deleted)=0));

Open in new window

Basically, you move the year matching requisite to the left join. That means that, when building the CltDue table, it will create it with just 2012 (or whatever you select) results, meaning anyone without a 2012 project will show up as null (you can use the ISNULL function to replace them with a default value).
0
 

Author Comment

by:ITMcmcpa
ID: 37777519
It is still just showing clients that have a project in 2012.  We made a dummy account that does not have a project to make sure that we have something show up.  This account does not show up at all, I'm assuming because it truly does not have a project in 2012.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37777570
The only reason I see for not showing up would be not having any match on the Employee table. I disregarded that one, because your requirements seemed to focus only on the Clients/CltDue interaction. You're left joining Clients with CltDue. That part works (you can try removing the Employee part). But you're then inner joining with Employees. Any record from the previous one that have no Employee match will be removed.
0
 

Author Comment

by:ITMcmcpa
ID: 37777910
That's a good point.  Let me check the dummy/test account.
0
 

Author Comment

by:ITMcmcpa
ID: 37778010
The employee id (Engcltptr) in the Clients table was missing.  I fixed that issue but the query still did not include the client's with missing projects.  I double checked the remaining fields that have criteria, such ans Engstatus and they do have the proper information.

So I started thinking about the WHERE statement and how it could impact the result.  I removed (CltDue.Deleted)=0) and I can now see the test client without the projects.

Is there a way to exclude records in the CltDue table that have been marked as deleted and still keep the clients that do not have projects / records in the CltDue table?
0
 

Author Comment

by:ITMcmcpa
ID: 37778046
Nevermind, the delete flag is not going to be an issue.  Thanks for your help.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 37780598
In answer to your question, yes, there is a way to do that. But that would depend on exactly how you want your data constructed. Easiest way would possibly be to do a Clients c1 LEFT JOIN (CltDue INNER JOIN Clients c2) where one of the conditions in c2 is the delete flag. That way, you'll keep all your Clients, but you'll filter those that have projects in 2012 without the flag.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'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 …
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

13 Experts available now in Live!

Get 1:1 Help Now