Avatar of bfuchs
bfuchs
Flag for United States of America asked on

Query comparing two tables with different format needed.

Hi Experts,

I need to compare two files containing same data with different format.

They have following columns

Patients Name
Caregiver name
Visit Date
Visit time from
Visit Time to

would need to get a list of all unmatched records.
its only considered a match if all 5 fields are the same.

Attached sample of those files, where record in both files is considered a match.

Here are the differences, one of the files has the following
1- first and last name concatenated into one field.
2- has data in parenthesis after the names which should be ignored.
3- format of time is 1459-2304
Visit-Report.xls
V_TestHHASchedule.xls
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
PortletPaul

Is this data stored in Access or in SQL Server?
i.e. which database syntax do you want for the comparison?

what date/time is "1459-2304"  in yyyy-mm-dd hh:mm format ? (i.e. how is this compared to a normal date/time?)
bfuchs

ASKER
hi,
currently data is coming from 3rd party in Excel format, can import anywhere...
would prefer Access if possible...
1459-2304 is HHMM-HHMM
Thanks,
Ben
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
@PortletPaul,
Wonder why I did not get an email notifying your post, neither did it showed up under notifications, perhaps something is malfunctioning in EE.
Along these lines in SQL Server 2012 or later.
So you mean its not compatible with earlier versions of SQL?
Currently I use SQL 2008, what entails to convert it to that version?
Thanks,
Ben
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
PortletPaul

the format() function was introduced in SQL2012 (which is 6 years ago already)

Instead of FORMAT() you have to use CONVERT() instead which isn't that simple to do I'm afraid, but its because your db version is old.
see: https://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html?headerLink=workspace_article
Mark Wills

For the string columns, I would use CHARINDEX() in SQL or INSTR() in Access and check if those columns (from V_TestHHASchedule) EXISTS in the VISITS-REPORT WHERE charindex(Patients_Firstname,Patientname) > 0 and charindex(Patients_Lastname,Patientname) > 0  (etc).

Access has INSTR but (more logically) the search string is first and the item being searched for is second ie INSTR(patientname,patients_firstname) > 0 and INSTR(patientname,patients_lastname) > 0  (etc)

Same again for caregiver (checking if it is contained within Aidename) and any other string.

Now for VisitDate, I would use datediff() e.g. in SQL Server : datediff(d,Schedule_VisitDate,visitdate) = 0
In Access : datediff("d",Schedule_VisitDate,visitdate) = 0

So that leaves us with the time....

And going to have to convert to the VisitTime format

In Access :   Format(Schedule_VisitStartTime,"hhnn")+"-"+Format(Schedule_VisitEndTime,"hhnn")  = VisitTime

and while we can use Format in SQL Server - it only happened from SQL 2012 and is basically the same as Access (use single quotes)

In SQL2008 : replace(convert(varchar(5),Schedule_VisitStartTime,108)+'-'+convert(varchar(5),Schedule_VisitEndTime,108),':','')

So, the basic approach in either SQL Server or Access will be :

Select *
from  V_TestHHASchedule V
WHERE EXISTS (SELECT NULL FROM VISITS-REPORT R WHERE <the above conditions hold true>)

and that will show rows from  V_TestHHASchedule that are also in Visits_Report

to show non-matching, then use : NOT EXISTS

Does that make sense ?
SOLUTION
Mark Wills

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
@Mark,
Just testing your SQL version, and no records comes up.
when changing it to not exists then all records come up.
also we must use the not exists as I'm looking for the unmatched ones...
Thanks,
Ben
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER
@Mark,

The time conversion is definitely a not functioning properly.

see below

SELECT V.Schedule_VisitStartTime,V.Schedule_VisitEndTime,replace(convert(varchar(5),V.Schedule_VisitStartTime,108)+'-'+convert(varchar(5),V.Schedule_VisitEndTime,108),':','')
FROM V_TestHHASchedule AS V

Open in new window

11/10/2018 7:00:00 AM      11/10/2018 7:00:00 PM      11/10-11/10
11/17/2018 7:15:00 AM      11/17/2018 7:00:00 PM      11/17-11/17
11/2/2018 7:00:00 AM      11/2/2018 9:00:00 AM      11/2/-11/2/
11/2/2018 2:00:00 PM      11/2/2018 7:00:00 PM      11/2/-11/2/
11/9/2018 7:15:00 AM      11/9/2018 9:00:00 AM      11/9/-11/9/
11/9/2018 1:45:00 PM      11/9/2018 7:15:00 PM      11/9/-11/9/
11/9/2018 9:00:00 AM      11/9/2018 1:45:00 PM      11/9/-11/9/
11/3/2018 7:00:00 AM      11/3/2018 7:00:00 PM      11/3/-11/3/
11/8/2018 7:00:00 AM      11/8/2018 2:00:00 PM      11/8/-11/8/
11/8/2018 2:00:00 PM      11/8/2018 7:00:00 PM      11/8/-11/8/
11/2/2018 9:00:00 AM      11/2/2018 2:00:00 PM      11/2/-11/2/
11/16/2018 7:15:00 AM      11/16/2018 9:00:00 AM      11/16-11/16

Thanks,
Ben
bfuchs

ASKER
Thanks experts,
I ended up using a combination of both.

SELECT V.*
FROM V_TestHHASchedule AS V
WHERE  not Exists (SELECT NULL FROM [VisitReport] R
                       --WHERE  charindex(V.patients_firstname,R.patientname) > 0
                       where RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ) = RTRIM( LEFT( v.patients_lastname, CHARINDEX( ' (', v.patients_lastname + ' (' ) ) ) + ' ' + RTRIM( LEFT( v.patients_firstname, CHARINDEX( ' (', v.patients_firstname + ' (' ) ) )
                       and RTRIM( LEFT( r.aideName, CHARINDEX( ' (', r.aideName + ' (' ) ) ) = RTRIM( LEFT( v.caregivers_lastname, CHARINDEX( ' (', v.caregivers_lastname + ' (' ) ) ) + ' ' + RTRIM( LEFT( v.caregivers_firstname, CHARINDEX( ' (', v.caregivers_firstname + ' (' ) ) )

                       and datediff(d,V.Schedule_VisitDate,R.visitdate) = 0
                     
                       and replace(convert(varchar(5),V.Schedule_VisitStartTime,108)+'-'+convert(varchar(5),V.Schedule_VisitEndTime,108),':','') = R.scheduled

Thank you!
bfuchs

ASKER
Hi Experts,

One fix if you dont mind...
The file V_TestHHASchedule when its midnight it comes with only a date portion without the times.
How can that be converted to 00:00 so it will match the other file?

Thanks,
Ben
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mark Wills

It all depends on datatypes.

I too found that the time portion was mucking up after importing the spreadsheet.

Found that it was coming in as varchar. A simple change to the structure of the table (keeping data in place) :
alter table V_TestHHASchedule alter column schedule_visitdate datetime
alter table V_TestHHASchedule alter column schedule_visitstarttime datetime
alter table V_TestHHASchedule alter column schedule_visitendtime datetime

Open in new window

And everthing worked as expected....

It does depend a bit on how that table is structured and populated.

This is how I imported :
set dateformat MDY

select * into V_TestHHASchedule
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;Database=c:\mrwtest\ee\V_TestHHASchedule.xls;HDR=yes', 'SELECT * FROM [V_TestHHASchedule$]') as a

go

select * from INFORMATION_SCHEMA.columns where table_name = 'V_TestHHASchedule'

go

alter table V_TestHHASchedule alter column schedule_visitdate datetime
alter table V_TestHHASchedule alter column schedule_visitstarttime datetime
alter table V_TestHHASchedule alter column schedule_visitendtime datetime

go

Open in new window

By way of comparison, look at what happens when convert works with a string vs a datetime :
select replace(convert(varchar(5),'2018-12-12 07:08:09.123',108)+'-'+convert(varchar(5),'2018-12-12 08:09:10.123',108),':','')

select replace(convert(varchar(5),cast('2018-12-12 07:08:09.123' as datetime),108)+'-'+convert(varchar(5),cast('2018-12-12 08:09:10.123' as datetime),108),':','')

Open in new window

In the string date, the STYLE CODE of 108 basically tells Convert how to read the string. When it is a datetime, the style code of 108 tells converrt how to format the datetime.

Hope that helps....
PortletPaul

Life is simpler when the format() function becomes available.
(Hint Hint)
Mark Wills

In another thread....

Ben has said they have to comply with the lowest common denominator - SQL 2008

It was strongly suggested to plan upgrades to SQL 2012 or more recent.

There is so much more to offer in SQL2012+

And by the way, if it was a varchar column, then the format(myvarchar,'date/time specifiers') would fail in SQL Server.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
bfuchs

ASKER