Avatar of SQLSearcher

asked on 

SQL Scriprt find non matching records

Hello Experts Exchange
I have a SQL query to find non matching records between two tables.  The tables I have is TableA which has employee's and their booked hours, TableB which is employee's clocked hours.

I am finding that in TableA there is more booked hours than clocked hours, but I am unable to find the over booked hours using a query.  This is because there is no record in TableB.

How I match records is by the First Name=First Name, Surname=Surname, and Date=Date.

Here is a query I wrote to find non matching records. But it does not find all non matching records.

Select a.[First Name],a.[Surname],
		a.[Clock Number],a.[Cost Centre], b.Location,
		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
	left join [dbo].[T&A_Temp_Primetime] b 
		on a.[First Name]= b.[First Name] 
		and a.surname = b.surname
		and a.[date] = b.[date]
Where a.[TimeCal] > 0
and b.[Hours] is null
order by a.[Cost Centre],[shift]

Open in new window

How can I change this query to get me the over booked hours?


Microsoft SQL ServerSQL

Avatar of undefined
Last Comment

8/22/2022 - Mon