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
314 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
[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
  • 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
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

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
 

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

622 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