Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.
COURSE of the MONTH
7 days, 19 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Scriprt find non matching records
Posted on 2014-12-03
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.
How can I change this query to get me the over booked hours?
Regards
SQLSearcher
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,
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
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]
How can I change this query to get me the over booked hours?
Regards
SQLSearcher
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
6
4
3
13 Comments
Try this:
Select a.[First Name],a.[Surname],
a.[Clock Number],a.[Cost Centre], b.Location,
a.[shift],
Convert(varchar,a.[Date],103) as [Date],
a.[TimeCal] as [Losses Attendance Hours],
b.[Hours] as [PrimetimeHours],
a.[TimeCal] - coalesce(b.[Hours],0) 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 or b.[hours]<>a.[timecall])
order by a.[Cost Centre],[shift]
Active today
I don't know why are you trying to select field from TableB since they should be NULL (no exist, right?).
Try this query and check if is what you need:
Try this query and check if is what you need:
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]
from [dbo].[T&A_Temp] a
Where a.[TimeCal] > 0 and
Not Exists (SELECT 1
FROM [dbo].[T&A_Temp_Primetime] b
WHERE a.[First Name]= b.[First Name] and a.surname = b.surname and a.[date] = b.[date]
and b.[Hours] is null)
order by a.[Cost Centre],a.[shift]
Hello Vitor
Yes you are right I do not need columns from TableB as they will be null.
Your script does not work it brings back matching records, it returned 2345 records, and I'm expecting to see less than 100 records.
Regards
SQLSearcher
Yes you are right I do not need columns from TableB as they will be null.
Your script does not work it brings back matching records, it returned 2345 records, and I'm expecting to see less than 100 records.
Regards
SQLSearcher
Active today
Can you please post a sample of data so we can make tests?
Just a subset of the data and then you need to mark which one matches and which one not.
Just a subset of the data and then you need to mark which one matches and which one not.
Is it the case that non of the non-matched records exist in table B, or will some exist there but have a different value for hours?
Hello Vitor
Please find Sample Data attached in Excel file, I have labelled the sheets TableA and TableB.
Some Records are missing for the employee Rafal Dudek.
Regards
SQLSearcher
Sample-Data.xls
Please find Sample Data attached in Excel file, I have labelled the sheets TableA and TableB.
Some Records are missing for the employee Rafal Dudek.
Regards
SQLSearcher
Sample-Data.xls
Hello SimonAdept
The data I am after is where a record does not exist in TableB. I have already got a query to find where hours do not match up.
Regards
SQLSearcher
The data I am after is where a record does not exist in TableB. I have already got a query to find where hours do not match up.
Regards
SQLSearcher
Active today
Which are the records that should be returned by the query?
TABLE A:
Rafal Dudek 0 0 0 01.11.2014 0 Htr6 (586) NULL Red 0 2131
Rafal Dudek 0 0 0 02.11.2014 0 Htr6 (586) NULL Red 0 2132
Rafal Dudek 0 0 7.5 03.11.2014 7.5 Htr6 (586) NULL Red 0 2133
Rafal Dudek 0 0 7.5 04.11.2014 7.5 Htr6 (586) NULL Red 0 2134
Rafal Dudek 0 0 7.5 05.11.2014 7.5 Htr6 (586) NULL Red 0 2135
Rafal Dudek 0 0 7.5 06.11.2014 7.5 Htr6 (586) NULL Red 0 2136
Rafal Dudek 0 0 7.5 07.11.2014 7.5 Htr6 (586) NULL Red 0 2137
Rafal Dudek 0 0 0 08.11.2014 0 Htr6 (586) NULL Red 0 2138
Rafal Dudek 5 0 0 09.11.2014 5 Htr6 (586) NULL Red 0 2139
Rafal Dudek 0 0 0 10.11.2014 0 Htr6 (586) NULL Red 0 2140
Rafal Dudek 0 0 7.5 11.11.2014 7.5 Htr6 (586) NULL Red 0 2141
Rafal Dudek 0 0 7.5 12.11.2014 7.5 Htr6 (586) NULL Red 0 2142
Rafal Dudek 0 0 7.5 13.11.2014 7.5 Htr6 (586) NULL Red 0 2143
Rafal Dudek 0 0 7.5 14.11.2014 7.5 Htr6 (586) NULL Red 0 2144
or TABLE B:
586 - Heater 6 Morning Rafal Dudek 02.11.2014 0 Rafal Dudek NULL 506
586 - Heater 6 Morning Rafal Dudek 01.11.2014 0 Rafal Dudek NULL 505
586 - Heater 6 Afternoon Rafal Dudek 03.11.2014 7.5 Rafal Dudek NULL 2621
TABLE A:
Rafal Dudek 0 0 0 01.11.2014 0 Htr6 (586) NULL Red 0 2131
Rafal Dudek 0 0 0 02.11.2014 0 Htr6 (586) NULL Red 0 2132
Rafal Dudek 0 0 7.5 03.11.2014 7.5 Htr6 (586) NULL Red 0 2133
Rafal Dudek 0 0 7.5 04.11.2014 7.5 Htr6 (586) NULL Red 0 2134
Rafal Dudek 0 0 7.5 05.11.2014 7.5 Htr6 (586) NULL Red 0 2135
Rafal Dudek 0 0 7.5 06.11.2014 7.5 Htr6 (586) NULL Red 0 2136
Rafal Dudek 0 0 7.5 07.11.2014 7.5 Htr6 (586) NULL Red 0 2137
Rafal Dudek 0 0 0 08.11.2014 0 Htr6 (586) NULL Red 0 2138
Rafal Dudek 5 0 0 09.11.2014 5 Htr6 (586) NULL Red 0 2139
Rafal Dudek 0 0 0 10.11.2014 0 Htr6 (586) NULL Red 0 2140
Rafal Dudek 0 0 7.5 11.11.2014 7.5 Htr6 (586) NULL Red 0 2141
Rafal Dudek 0 0 7.5 12.11.2014 7.5 Htr6 (586) NULL Red 0 2142
Rafal Dudek 0 0 7.5 13.11.2014 7.5 Htr6 (586) NULL Red 0 2143
Rafal Dudek 0 0 7.5 14.11.2014 7.5 Htr6 (586) NULL Red 0 2144
or TABLE B:
586 - Heater 6 Morning Rafal Dudek 02.11.2014 0 Rafal Dudek NULL 506
586 - Heater 6 Morning Rafal Dudek 01.11.2014 0 Rafal Dudek NULL 505
586 - Heater 6 Afternoon Rafal Dudek 03.11.2014 7.5 Rafal Dudek NULL 2621
Hi Vitor
These should be the ones that return.
TableA
Rafal Dudek 0 0 7.5 04.11.2014 7.5 Htr6 (586) NULL Red 0 2134
Rafal Dudek 0 0 7.5 05.11.2014 7.5 Htr6 (586) NULL Red 0 2135
Rafal Dudek 0 0 7.5 06.11.2014 7.5 Htr6 (586) NULL Red 0 2136
Rafal Dudek 0 0 7.5 07.11.2014 7.5 Htr6 (586) NULL Red 0 2137
Rafal Dudek 0 0 0 08.11.2014 0 Htr6 (586) NULL Red 0 2138
Rafal Dudek 5 0 0 09.11.2014 5 Htr6 (586) NULL Red 0 2139
Rafal Dudek 0 0 0 10.11.2014 0 Htr6 (586) NULL Red 0 2140
Rafal Dudek 0 0 7.5 11.11.2014 7.5 Htr6 (586) NULL Red 0 2141
Rafal Dudek 0 0 7.5 12.11.2014 7.5 Htr6 (586) NULL Red 0 2142
Rafal Dudek 0 0 7.5 13.11.2014 7.5 Htr6 (586) NULL Red 0 2143
Rafal Dudek 0 0 7.5 14.11.2014 7.5 Htr6 (586) NULL Red 0 2144
Regards
SQLSearcher
These should be the ones that return.
TableA
Rafal Dudek 0 0 7.5 04.11.2014 7.5 Htr6 (586) NULL Red 0 2134
Rafal Dudek 0 0 7.5 05.11.2014 7.5 Htr6 (586) NULL Red 0 2135
Rafal Dudek 0 0 7.5 06.11.2014 7.5 Htr6 (586) NULL Red 0 2136
Rafal Dudek 0 0 7.5 07.11.2014 7.5 Htr6 (586) NULL Red 0 2137
Rafal Dudek 0 0 0 08.11.2014 0 Htr6 (586) NULL Red 0 2138
Rafal Dudek 5 0 0 09.11.2014 5 Htr6 (586) NULL Red 0 2139
Rafal Dudek 0 0 0 10.11.2014 0 Htr6 (586) NULL Red 0 2140
Rafal Dudek 0 0 7.5 11.11.2014 7.5 Htr6 (586) NULL Red 0 2141
Rafal Dudek 0 0 7.5 12.11.2014 7.5 Htr6 (586) NULL Red 0 2142
Rafal Dudek 0 0 7.5 13.11.2014 7.5 Htr6 (586) NULL Red 0 2143
Rafal Dudek 0 0 7.5 14.11.2014 7.5 Htr6 (586) NULL Red 0 2144
Regards
SQLSearcher
SELECT
TableA.MergeID, TableA.Date
, TableA.FirstName
, TableA.Surname
, TableA.Attendance
, TableB.Hours
FROM TableA LEFT JOIN TableB ON (TableA.FirstName = TableB.[First Name]) AND (TableA.Surname = TableB.Surname) AND (TableA.[Date] = TableB.[Date])
WHERE (((TableB.Date) Is Null));
Returns 39 records. 8 of those have more than zero hours in the attendance column
This query gets all cols from TableA where no record in TableB and > 0 in attendance col
SELECT TableA.*, TableA.Attendance
FROM TableA LEFT JOIN TableB ON (TableA.FirstName = TableB.[First Name]) AND (TableA.Surname = TableB.Surname) AND (TableA.[Date] = TableB.[Date])
WHERE (((TableB.Date) Is Null) AND ((TableA.Attendance)>0));
Active today
But in your main post you have the following criteria:
a.[TimeCal] > 0 and b.[Hours] is null
Since you don't have null values in b.Hours that's why is returning more that expected. If you don't want to have those filter you can't remove them, like this:
a.[TimeCal] > 0 and b.[Hours] is null
Since you don't have null values in b.Hours that's why is returning more that expected. If you don't want to have those filter you can't remove them, like this:
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]
from [dbo].[T&A_Temp] a
Where Not Exists (SELECT 1
FROM [dbo].[T&A_Temp_Primetime] b
WHERE a.[First Name]= b.[First Name] and a.surname = b.surname and a.[date] = b.[date])
order by a.[Cost Centre],a.[shift]
Featured Post
Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month7 days, 19 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.