asked on

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

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!

Cluskitt

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

Cluskitt

I meant [CltDue].[CDDue] IS NULL, sorry for the typo

Leo Torres

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

ITMcmcpa

ASKER

Unfortunately neither of these show the clients that do not have a project for the queried year.

Cluskitt

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.

ITMcmcpa

ASKER

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

Cluskitt

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?

ITMcmcpa

ASKER

Just ones that do not have a project in 2012 if that is the year I enter for the parameter.

ASKER CERTIFIED SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITMcmcpa

ASKER

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.

Cluskitt

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.

ITMcmcpa

ASKER

That's a good point. Let me check the dummy/test account.

ITMcmcpa

ASKER

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?

ITMcmcpa

ASKER

Nevermind, the delete flag is not going to be an issue. Thanks for your help.

Cluskitt

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.