asked on

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

Vitor Montalvão

What for you need the having count(*) > 30 clause in the subquery?
Did you try to run the query without that clause?

Vitor Montalvão

And I can't also see a relationship between the subquery and the main query.

SQLSearcher

ASKER

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

Vitor Montalvão

Can you explain better what you are trying to achieve?
That query doesn't looks me that will work, that's why.

SQLSearcher

ASKER

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial

SQLSearcher

ASKER

Thank you very much for your help.