SQL Script to remove a list of names from query

Hello Experts Exchange
I have a SQL query that I want to remove a list of names using a sub-query, but when I run my query using the sub-query I have not records return, and there should be records that return.

Here is my query;

Select a.[First Name],a.[Surname],
		a.[Clock Number],a.[Cost Centre], a.[shift],
		Convert(varchar,a.[Date],103) as [Date],
		a.[TimeCal] as [Losses Attendance Hours],b.[Hours] as [PrimetimeHours], 
		a.[TimeCal] - b.[Hours] as [Difference]
from [dbo].[T&A_Temp] a
inner join [dbo].[T&A_Temp_Primetime] b
on a. [First Name]= b.[First Name]
and a.surname = b.surname
Where a.[TimeCal] <> b.[Hours]
and NOT Exists (select [first name],Surname
				  from [T&A_Temp]
				  group by [first name],Surname
				  having count(*) > 30)
order by a.[Cost Centre]

Open in new window


Can anyone help me with my query please?

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Then you just missed the WHERE clause to join the subquery with the main query. Try this one:
Select a.[First Name],a.[Surname],
		a.[Clock Number],a.[Cost Centre], a.[shift],
		Convert(varchar,a.[Date],103) as [Date],
		a.[TimeCal] as [Losses Attendance Hours],b.[Hours] as [PrimetimeHours], 
		a.[TimeCal] - b.[Hours] as [Difference]
from [dbo].[T&A_Temp] a
	inner join [dbo].[T&A_Temp_Primetime] b 
		on a.[First Name]= b.[First Name] and a.surname = b.surname
Where a.[TimeCal] <> b.[Hours]
	and NOT Exists (select c.[first name],c.Surname
				  from [T&A_Temp] c
				  where a.[First Name]= c.[First Name] and a.surname = c.surname
				  group by c.[first name],c.Surname
				  having count(1) > 30)
order by a.[Cost Centre]

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What for you need the having count(*) > 30 clause in the subquery?
Did you try to run the query without that clause?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
And I can't also see a relationship between the subquery and the main query.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SQLSearcherAuthor Commented:
Hello Vitor
I run it without the having count(*) > 30 clause but same result no records.  I need the clause so it selects the right names.

Regards

SQLSearcher
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you explain better what you are trying to achieve?
That query doesn't looks me that will work, that's why.
0
 
SQLSearcherAuthor Commented:
Hi Vitor
So my main query joins table A and B using First Name, Surname and Date.  This gives me a report of where my losses list does not match my primetime list.  This query works fine when the sub-query is not in the script.

Now I have a second query a list of users that appear on the list more than 30 times, this means they are assigned to more than one cost centre.  This is my sub-query, it works fine on its own it returns 10 first name and surnames.

My query is wrong when put together I am looking for the right syntax.

I want the 10 names returned in my sub-query to be removed from my main list.

Regards

SQLSearcher
0
 
SQLSearcherAuthor Commented:
Thank you very much for your help.
0
All Courses

From novice to tech pro — start learning today.