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
310 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

770 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