WHERE clause to get most recent value from another table 3

[upplepop]

I have two tables, a client table and an Entry table which stores transactions with a client. A client will most likely have several entries in the Entry table, but are only allowed one entry per day.

I need to make a query that will list all the clients in the Client table EXCEPT those that have an entry date of today. This will be used to make a drop-down box, and I don't want people accidently putting in more than one entry per client per day.

Here is what I have so far:

SELECT ClientID, [LastName] + ', ' + [FirstName] AS ClientName  FROM Client WHERE (CONVERT(char(10), GETDATE(), 101) <> CONVERT(char(10), EntryDate, 101)) ORDER BY ClientName

The problem is that this query will display clients who have been entered today if they also have previous Entry Dates. My best guess on how to fix this problem is to create a nested query that looks for the maximum value of the Entry Table and compares the current date against that.

I'd greatly appreciate your help!


Here is an example of what the tables look like:

Client table:

ClientID FName LName
-------- ----- -----
1000     Bob   Smith
1001     John  Doe

Entry table:

EntryID ClientID EntryDate
------- -------- ---------
0001    1000     01/01/2004
0002    1000     03/02/2004
0003    1001     03/02/2004

 

[Pattycake245]

Just so I am understanding you correctly, if today was 03/02/2004, according to your example data, no employees would be returned?

Tim

 

[upplepop]

Just so I am understanding you correctly, if today was 03/02/2004, according to your example data, no employees would be returned?

That is correct because they already have an entry for today. As another example, if EntryID #0002 was removed, then Client #1000 (Bob) would be returned in the query because even though he has an Entry, it is not the current date.

 

[Pattycake245]

How about this then:

select a.clientid, ltrim(rtrim(a.fname))+' '+ltrim(rtrim(a.lname)) clientname
from Client a join Entry b on a.clientid=b.clientid
where b.entrydate in(select max(entrydate) from Entry where clientid=b.clientid and entrydate<> getdate())

Tim

 

[upplepop]

No, that's not quite working for me. Here is the exact query I'm using:

SELECT Client.ClientID, Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client 
INNER JOIN EntryExit ON Client.ClientID = EntryExit.ClientID
WHERE (EntryExit.EntryDate 
IN (SELECT MAX(EntryExit.Entrydate)
    FROM EntryExit
    WHERE Client.ClientID = EntryExit.ClientID 
    AND EntryExit.Entrydate <> getdate()))
ORDER BY ClientName

I am still getting clients who have an EntryDate of the current date.

 

[SQLSister]

Here's a possiblity to try. Use a derived table to get the clientIDs you want to exclude and then do a left join with a where clause of devidedtable.ID is null. Sort of like:

SELECT Client.ClientID, Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client 
left JOIN (SELECT clientID, MAX(EntryExit.Entrydate)
    FROM EntryExit    
	Group on clientID
    Having EntryExit.Entrydate < getdate())) a 
ON Client.ClientID = a.ClientID
where  a.ClientID is null

You will probably have to fool around with the dates to get the right records to exclude in the derived table as this would use the date and time and you need to exclude the time. Look at the convert statement or DatePart for how to do that.

Questions about posting. See faq183-874

 

[ESquared]

derived tables with a group by clause RULE THE WORLD

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[norgrovm]

I'm not sure that you need to do the joins to get the result that you're after. I'd get the client id's from the entry table that have not had an entry today, then use them in your WHERE clause to return the client names you want. You could alternatively use a SELECT DISTINCT in the sub-query instead of a GROUP BY, the key is that a subquery cannot return duplicate results to the outer query. eg below.

SELECT Client.ClientID, Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client 

WHERE Client.ClientID IN (SELECT EntryExit.ClientID    FROM EntryExit
    WHERE EntryExit.Entrydate <> getdate()
    GROUP BY EntryExit.ClientID )
ORDER BY ClientName

This might work faster than using a join, try it out and see...:0)

 

[norgrovm]

Hmmm i must be asleep this morning, i made the same mistake you made... :0)

so what i meant to say was... get the clients that were entered today from tbl.entry, then select those clients that aren't part of that sub-query result...

SELECT Client.ClientID, Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client 
WHERE Client.ClientID NOT IN (SELECT EntryExit.ClientID    FROM EntryExit
    WHERE EntryExit.Entrydate = getdate()
    GROUP BY EntryExit.ClientID )
ORDER BY ClientName

 

[indrahig]

or try this

SELECT Client.ClientID, 
  Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client
  LEFT OUTER JOIN (SELECT ClientId
     FROM EntryExit
     WHERE Entrydate = GetDate()
     GROUP BY ClientId) as B
WHERE B.ClientId is null
ORDER BY ClientName

 

[indrahig]

oops, sorry stupid mistake..
it should be like this:
(that's what you get if you did something in a hurry)

SELECT Client.ClientID, 
  Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client
  LEFT OUTER JOIN (SELECT ClientId
     FROM EntryExit
     WHERE Entrydate = GetDate()
     GROUP BY ClientId) as B on Client.ClientId = B.ClientId
WHERE B.ClientId is null
ORDER BY ClientName

 

[SQLSister]

norgrovm, most of the time a join is faster than an IN clause or a NOT IN clause.

Questions about posting. See faq183-874

 

[upplepop]

Thanks indrahig, your suggestion worked perfectly... with a little adjustment to the getdate() clause (see below).

I appreciate the help of all of those who posted in this thread!

SELECT Client.ClientID,
Client.LastName + ', ' + Client.FirstName AS ClientName
FROM Client
LEFT OUTER JOIN (SELECT ClientId
  FROM EntryExit
  WHERE (CONVERT(char(10), GETDATE(), 101) = CONVERT(char(10), EntryDate, 101))
  GROUP BY ClientId) as B on Client.ClientId = B.ClientId
  WHERE B.ClientId is null
ORDER BY ClientName

 

[ESquared]

That query doesn't look right. It has too many WHEREs.

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[SQLSister]

Esquared the number of wheres is right, one is on the derived table and one is on the overall query.

Questions about posting. See faq183-874

 

[ESquared]

Look at that! You're right.

I was misled by the indenting in my scan and didn't look closely enough at where the parentheses actually were.

-------------------------------------
A sacrifice is harder when no one knows you've made it.