Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Tips to figure out query results

Hi Experts,

I have the following query that compares two lists (in different formats) and gives the unmatched records.

SELECT   distinct v.*,replace(convert(varchar(5),V.Schedule_ScheduleStartTime,108)+'-'+convert(varchar(5),V.Schedule_ScheduleEndTime,108),':','')
FROM V_TestHHASchedule AS V
WHERE   not Exists (SELECT NULL FROM [VisitReport] R 

                       where 
                       replace(RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ),' ','') = replace(isnull(Patients_LastName,'') + isnull(Patients_MiddleName,'') + isnull(Patients_FirstName,''),' ','')
                       and replace(RTRIM( LEFT( r.aideName, CHARINDEX( ' (', r.aideName + ' (' ) ) ),' ','') = replace(isnull(Caregivers_LastName ,'') + isnull(Caregivers_MiddleName ,'') + isnull(Caregivers_FirstName,''),' ','')

                        and datediff(d,V.Schedule_VisitDate,R.visitdate) = 0

                        and replace(convert(varchar(5),V.Schedule_ScheduleStartTime,108)+'-'+convert(varchar(5),V.Schedule_ScheduleEndTime,108),':','') = R.scheduled
                       
                      
                      

)
and v.caregivers_lastname <>''
order by v.patients_lastname,v.patients_firstname, v.schedule_visitdate ;

Open in new window


I have some records showing up that cant figure out why.

Looking for some tips...

Here is an explanation of what the query supposed to be doing...
https://www.experts-exchange.com/questions/29126893/Query-comparing-two-tables-with-different-format-needed.html?headerLink=workspace_answered_questions

Thanks
Avatar of D B
D B
Flag of United States of America image
Any examples of data and what is showing up that shouldn't be. The question is about as vague as 'my car is acting funny.'
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi,
Attached Example.
Thanks,
Ben
Book1.xlsx
Avatar of PortletPaul
PortletPaul
Flag of Australia image
I assume we still need SQL Server 2008 compatible code.

While Excel is a fine tool, it is NOT a great way to display data precisely, especially dates and times
(my Excel defaults are probably different to your Excel defaults)

Here we need absolute precision because you are attempting to compare 2 sets of data. I will repeat my tables from the former question, which I believe should be relevant here: 
Visit-Report.xls
+-------+--------------+------------+--------------------------------------+----------------------------------+-----------------+-----------+--------------+-----------+----------+--------------+---------------+-------------+---------+------------+--------+------------+-------------+----+----------+---------------+---------+
| RowID | AltPatientID | MedicaidNo |             PatientName              |             AideName             | CoordinatorName | VisitDate | ScheduleTime | VisitTime | Duration | ContractName | ServiceCode_1 | PayrateCode | PayRate | Discipline | Billed | InvoiceNum | InvoiceDate | TS | TotalHrs | TotalDuration | Field22 |
+-------+--------------+------------+--------------------------------------+----------------------------------+-----------------+-----------+--------------+-----------+----------+--------------+---------------+-------------+---------+------------+--------+------------+-------------+----+----------+---------------+---------+
|     1 |              |            | test last p  test first p (WW-21345) | test c last test c first (11710) |                 | 19-Nov-18 |              | 1459-2304 | 08:05    |              |               |             |         |            |        |            |             |    |          |               |         |
+-------+--------------+------------+--------------------------------------+----------------------------------+-----------------+-----------+--------------+-----------+----------+--------------+---------------+-------------+---------+------------+--------+------------+-------------+----+----------+---------------+---------+

V_TestHHASchedule.xls
+---------------------+--------------------+-------------------+----------------------+---------------------+-------------------+------------------------------+--------------------+----------------------------+--------------------------+-------------------------+-------------------------+--------------------+----------------------+--------------------+----------------------+-----------------+
| Schedule_ScheduleID | Patients_FirstName | Patients_LastName | Caregivers_FirstName | Caregivers_LastName | Caregivers_Gender | Caregivers_TimeAttendancePIN | Schedule_VisitDate | Schedule_ScheduleStartTime | Schedule_ScheduleEndTime | Schedule_VisitStartTime |  Schedule_VisitEndTime  | Schedule_IsDeleted | Patients_AdmissionID | Schedule_PatientID | Schedule_CaregiverID | Patients_TeamID |
+---------------------+--------------------+-------------------+----------------------+---------------------+-------------------+------------------------------+--------------------+----------------------------+--------------------------+-------------------------+-------------------------+--------------------+----------------------+--------------------+----------------------+-----------------+
|                     | test first p       | test last p       | test c first         | test c last         |                   |                              | 2018-11-19 00:00   |                            |                          | 11/19/2018  14:59:00 PM | 11/19/2018  23:04:00 PM |                    |                      |                    |                      |                 |
+---------------------+--------------------+-------------------+----------------------+---------------------+-------------------+------------------------------+--------------------+----------------------------+--------------------------+-------------------------+-------------------------+--------------------+----------------------+--------------------+----------------------+-----------------+

Open in new window


Please confirm if the date/times in the second table are strings like this: "11/19/2018  14:59:00 PM "
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi,
I assume we still need SQL Server 2008 compatible code.
Correct.
visit date = '2018-11-10 00:00:00.000'
start time = '2018-11-10 23:00:00.000'
end time = '2018-11-11 07:00:00.000'
Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Assuming that the following columns are the data types shown
  ,v.Schedule_VisitDate           date or datetime
  ,v.Schedule_VisitStartTime      datetime
  ,v.Schedule_VisitEndTime        datetime
  ,r.VisitDate varchar
  ,r.VisitTime  varchar

Then I think the following query works. 
SELECT /* DISTINCT */
    REPLACE( CONVERT( varchar(5), V.Schedule_ScheduleStartTime, 108 ) + '-' + CONVERT( varchar(5), V.Schedule_ScheduleEndTime, 108 ), ':', '' ) sched
   , v.*
FROM [V_TestHHASchedule] AS V
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM [VisitReport] R
    WHERE ( v.Patients_LastName + ' ' + v.Patients_FirstName )               = ( replace(RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ),'  ',' ') )
    AND ( v.Caregivers_LastName + ' ' + v.Caregivers_FirstName )             = ( replace(RTRIM( LEFT( r.AideName, CHARINDEX( ' (', r.AideName + ' (' ) ) ),'  ',' ') )
    AND ( replace(convert(varchar(11),v.Schedule_VisitDate,6),' ','-') )     = r.VisitDate
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )

Open in new window

The big problem is that without inserts provided by you that are fully representative of the actual data there may be differences

see: db-fiddle
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

r.VisitDate varchar
No, This is a date.

Your query is showing way too much records.

Guess you are working on last thread suggestion, while here I posted some changes.
Included Patients middle name, and also the use of Isnull() may matter when concatenating fields that could be null.
will add them and see what happens...

Thanks,
Ben
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Oh just tried the following and all of the table records are showing up...

SELECT /* DISTINCT */
    REPLACE( CONVERT( varchar(5), V.Schedule_ScheduleStartTime, 108 ) + '-' + CONVERT( varchar(5), V.Schedule_ScheduleEndTime, 108 ), ':', '' ) sched
   , v.*
FROM [V_TestHHASchedule] AS V
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM [VisitReport] R
    WHERE ( v.Patients_LastName + v.Patients_FirstName + v.Patients_MiddleName )               = ( replace(replace(RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ),'  ',' ') ,' ',''))
    AND ( v.Caregivers_LastName +  v.Caregivers_FirstName + v.Caregivers_MiddleName )             = ( replace(replace(RTRIM( LEFT( r.AideName, CHARINDEX( ' (', r.AideName + ' (' ) ) ),'  ',' '),' ','') )
    AND ( replace(convert(varchar(11),v.Schedule_VisitDate,6),' ','-') )     = r.VisitDate
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )

Open in new window


Not sure what is the issue...

also tried this, got fewer records but still not good.

SELECT /* DISTINCT */
    REPLACE( CONVERT( varchar(5), V.Schedule_ScheduleStartTime, 108 ) + '-' + CONVERT( varchar(5), V.Schedule_ScheduleEndTime, 108 ), ':', '' ) sched
   , v.*
FROM [V_TestHHASchedule] AS V
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM [VisitReport] R
    WHERE ( isnull(v.Patients_LastName,'') + isnull(v.Patients_FirstName,'') + isnull(v.Patients_MiddleName,'') )               = ( replace(replace(RTRIM( LEFT( r.PatientName, CHARINDEX( ' (', r.PatientName + ' (' ) ) ),'  ',' ') ,' ',''))
    AND ( isnull(v.Caregivers_LastName,'') +  isnull(v.Caregivers_FirstName,'') + isnull(v.Caregivers_MiddleName,'') )             = ( replace(replace(RTRIM( LEFT( r.AideName, CHARINDEX( ' (', r.AideName + ' (' ) ) ),'  ',' '),' ','') )
    AND ( replace(convert(varchar(11),v.Schedule_VisitDate,6),' ','-') )     = r.VisitDate
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )

Open in new window


Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
This is a DATA related problem, you are comparing 2 sets of DATA.

For us to help you better we need better data to work with
I suggest you cease using spreadsheets because dates/times are a problem in Excel
and we have to guess what the data types are as well

Can you try using this site?
https://rextester.com/l/sql_server_online_compiler

Create the 2 tables and insert a few representative rows in each (without anything private!)
then press save, then it provides a URL
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi Paul,

I suggest you cease using spreadsheets because dates/times are a problem in Excel
Well since data is coming from Excel, and I just imported them into SQL in order to run this query...wouldn't it be better if I send them in original format (just obfuscating the names)?

The ones I posted earlier were exported from SQL to Excel, what means they already went thru two sets of exports/formats.

These are the create table scripts
USE [SkilledNursingVisitNotes]
GO

/****** Object:  Table [dbo].[V_TestHHASchedule]    Script Date: 12/12/2018 23:26:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[V_TestHHASchedule](
	[Schedule_ScheduleID] [float] NULL,
	[Patients_FirstName] [nvarchar](255) NULL,
	[Patients_LastName] [nvarchar](255) NULL,
	[Caregivers_FirstName] [nvarchar](255) NULL,
	[Caregivers_LastName] [nvarchar](255) NULL,
	[Caregivers_Gender] [nvarchar](255) NULL,
	[Caregivers_TimeAttendancePIN] [float] NULL,
	[Schedule_VisitDate] [datetime] NULL,
	[Schedule_ScheduleStartTime] [datetime] NULL,
	[Schedule_ScheduleEndTime] [datetime] NULL,
	[Schedule_VisitStartTime] [datetime] NULL,
	[Schedule_VisitEndTime] [datetime] NULL,
	[Schedule_IsDeleted] [nvarchar](255) NULL,
	[Patients_AdmissionID] [float] NULL,
	[Schedule_PatientID] [float] NULL,
	[Schedule_CaregiverID] [float] NULL,
	[Patients_TeamID] [float] NULL,
	[Patients_MiddleName] [nvarchar](255) NULL,
	[Caregivers_MiddleName] [nvarchar](255) NULL
) ON [PRIMARY]

GO
----------------------------------

CREATE TABLE [dbo].[VisitReport](
	[RowID] [float] NULL,
	[AltPatientID] [nvarchar](255) NULL,
	[MedicaidNo] [nvarchar](255) NULL,
	[PatientName] [nvarchar](255) NULL,
	[AideName] [nvarchar](255) NULL,
	[CoordinatorName] [nvarchar](255) NULL,
	[VisitDate] [datetime] NULL,
	[Scheduled] [nvarchar](255) NULL,
	[VisitTime] [nvarchar](255) NULL,
	[Duration] [datetime] NULL,
	[ContractName] [nvarchar](255) NULL,
	[ServiceCode_1] [nvarchar](255) NULL,
	[PayrateCode] [nvarchar](255) NULL,
	[PayRate] [money] NULL,
	[Discipline] [nvarchar](255) NULL,
	[Billed] [nvarchar](255) NULL,
	[InvoiceNum] [float] NULL,
	[InvoiceDate] [datetime] NULL,
	[TS] [nvarchar](255) NULL,
	[TotalHrs] [nvarchar](255) NULL,
	[TotalDuration] [nvarchar](255) NULL
) ON [PRIMARY]

GO

Open in new window


Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Was not aware they started life as Excel sheets.

The DATA involves comparing the NAMES which are in totally different formats, and despite some effort to align these on the basis of what has been supplied in the past (and which works by the way)  I am unable to guess what variations may exist in the DATA that only you have access to.  The issue is mostly DATA related,thus limiting what can be offered.

Perhaps this will help....
Divide the tests into "names" and "time"

IF the times DO match, then the names should also match, but if they don't show these
SELECT
    *
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientName LIKE v.Patients_LastName + '%'
    AND r.PatientName LIKE '%' + v.Patients_LastName + '%'
)
/* matching by "time" */
AND EXISTS (
    SELECT
        NULL
    FROM [VisitReport] R
    WHERE  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )

Open in new window

Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

This query does not return any records, how do we proceed?

Was not aware they started life as Excel sheets
Actually they originally come from two different systems, and both offer a way to export to either Excel (xlsx) or CSV.
I tried first CSV but had difficult time importing them into SQL.
Would it help if I use CSV instead?

btw, if I use something like this to obfuscate data, would it help?
https://www.experts-exchange.com/questions/29127578/script-to-convert-all-characters-in-excel-sheet-to-A.html?headerLink=workspace_answered_questions

Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Regarding "sample data"

Really I believe this is up to you, particularly obfuscation. There aren't that many columns involved (i.e. most columns can be ignored for this question) and all we need are sufficient SAMPLES . The point here being that if you supply too much data will you be able to verify the results, but if too little data will we miss things.

Bottom-line is that, with data related questions, the onus is on the owner of that data
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Here you got some data, let me know if you can work with it.
Copied original data, and obfuscated names. (however using same pattern in both, so they should match).
Thanks,
Ben
EE-V_TestHHASchedule.xlsx
EE-VisitReport.xlsx
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Please visit this link:
Permanent link: https://rextester.com/DEOM23106

There you will find 3 operating queries, as seen here: 
SELECT
    'join' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime, r.PatientsName, r.VisitDate, r.VisitTime
FROM V_TestHHASchedule v
inner join VisitReport r
    ON r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime 
;

SELECT
    'exists' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )  
;

SELECT DISTINCT
    'not exists' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Open in new window

The results of these are as follows: 
|   | qry  | Patients_LastName | Patients_FirstName | Schedule_VisitDate  | Schedule_ScheduleStartTime |       PatientsName        |      VisitDate      | VisitTime |
|---|------|-------------------|--------------------|---------------------|----------------------------|---------------------------|---------------------|-----------|
| 1 | join | bSGHbR            | NbdHb              | 10.11.2018 00:00:00 | 10.11.2018 23:00:00        | bSGHbR NbdHb (WGC-901995) | 10.11.2018 00:00:00 | 2300-0700 |

|    |  qry   | Patients_LastName | Patients_FirstName | Schedule_VisitDate  | Schedule_ScheduleStartTime |
|----|--------|-------------------|--------------------|---------------------|----------------------------|
|  1 | exists | bSGHbR            | NbdHb              | 10.11.2018 00:00:00 | 10.11.2018 23:00:00        |


|    |    qry     | Patients_LastName | Patients_FirstName | Schedule_VisitDate  | Schedule_ScheduleStartTime |
|----|------------|-------------------|--------------------|---------------------|----------------------------|
|  1 | not exists | bSGHbR            | NbdHb              | 11.11.2018 00:00:00 | 11.11.2018 23:15:00        |
|  2 | not exists | bSGHbR            | NbdHb              | 12.11.2018 00:00:00 | 12.11.2018 23:00:00        |
|  3 | not exists | bSGHbR            | NbdHb              | 13.11.2018 00:00:00 | 13.11.2018 23:00:00        |
|  4 | not exists | bSGHbR            | NbdHb              | 14.11.2018 00:00:00 | 14.11.2018 23:00:00        |
|  5 | not exists | bSGHbR            | NbdHb              | 15.11.2018 00:00:00 | 15.11.2018 23:00:00        |
|  6 | not exists | bSGHbR            | NbdHb              | 16.11.2018 00:00:00 | 16.11.2018 23:00:00        |
|  7 | not exists | bSGHbR            | NbdHb              | 17.11.2018 00:00:00 | 17.11.2018 23:00:00        |
|  8 | not exists | bSGHbR            | NbdHb              | 18.11.2018 00:00:00 | 18.11.2018 23:00:00        |
|  9 | not exists | bSGHbR            | NbdHb              | 19.11.2018 00:00:00 | 19.11.2018 23:00:00        |
| 10 | not exists | bSGHbR            | NbdHb              | 20.11.2018 00:00:00 | 20.11.2018 23:00:00        |
| 11 | not exists | bSGHbR            | NbdHb              | 21.11.2018 00:00:00 | 21.11.2018 23:00:00        |
| 12 | not exists | bSGHbR            | NbdHb              | 22.11.2018 00:00:00 | 22.11.2018 23:00:00        |
| 13 | not exists | bSGHbR            | NbdHb              | 23.11.2018 00:00:00 | 23.11.2018 23:00:00        |
| 14 | not exists | bSGHbR            | NbdHb              | 24.11.2018 00:00:00 | 24.11.2018 23:00:00        |
| 15 | not exists | bSGHbR            | NbdHb              | 25.11.2018 00:00:00 | 25.11.2018 23:00:00        |
| 16 | not exists | bSGHbR            | NbdHb              | 26.11.2018 00:00:00 | 26.11.2018 23:00:00        |
| 17 | not exists | bSGHbR            | NbdHb              | 27.11.2018 00:00:00 | 27.11.2018 23:00:00        |
| 18 | not exists | bSGHbR            | NbdHb              | 28.11.2018 00:00:00 | 28.11.2018 23:00:00        |
| 19 | not exists | bSGHbR            | NbdHb              | 29.11.2018 00:00:00 | 29.11.2018 23:00:00        |
| 20 | not exists | bSGHbR            | NbdHb              | 30.11.2018 00:00:00 | 30.11.2018 23:00:00        |
| 21 | not exists | bSGHbR            | NbdHb              | 01.12.2018 00:00:00 | 01.12.2018 23:00:00        |
| 22 | not exists | bSGHbR            | NbdHb              | 02.12.2018 00:00:00 | 02.12.2018 23:00:00        |
| 23 | not exists | bSGHbR            | NbdHb              | 03.12.2018 00:00:00 | 03.12.2018 23:00:00        |
| 24 | not exists | bSGHbR            | NbdHb              | 04.12.2018 00:00:00 | 04.12.2018 23:00:00        |
| 25 | not exists | bSGHbR            | NbdHb              | 05.12.2018 00:00:00 | 05.12.2018 23:00:00        |
| 26 | not exists | bSGHbR            | NbdHb              | 06.12.2018 00:00:00 | 06.12.2018 23:00:00        |
| 27 | not exists | bSGHbR            | NbdHb              | 07.12.2018 00:00:00 | 07.12.2018 23:00:00        |
| 28 | not exists | bSGHbR            | NbdHb              | 08.12.2018 00:00:00 | 08.12.2018 23:00:00        |
| 29 | not exists | bSGHbR            | NbdHb              | 09.12.2018 00:00:00 | 09.12.2018 23:00:00        |
| 30 | not exists | bSGHbR            | NbdHb              | 10.12.2018 00:00:00 | 10.12.2018 22:00:00        |

Open in new window

So there is only one row in V_TestHHASchedule  that also exists in VisitReport

I simply used the same matching logic I had previously provided - just re-arranging the pieces of that -  to produce these queries and results, so I feel as if I have done all I can.

For anyone who wants to work on this, sample data and the queries:
CREATE TABLE V_TestHHASchedule(
   Patients_FirstName         VARCHAR(5) NOT NULL
  ,Patients_LastName          VARCHAR(6) NOT NULL
  ,Caregivers_FirstName       VARCHAR(10) NOT NULL
  ,Caregivers_LastName        VARCHAR(7) NOT NULL
  ,Schedule_VisitDate         DATE  NOT NULL
  ,Schedule_ScheduleStartTime datetime NOT NULL
  ,Schedule_ScheduleEndTime   datetime NOT NULL
  ,Schedule_VisitStartTime    datetime
  ,Schedule_VisitEndTime      datetime
);
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRTHb','GfSTbS','2018-11-20','2018-11-20 23:00','2018-11-21 07:00','2018-11-20 23:00','2018-11-21 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRTHb','GfSTbS','2018-11-13','2018-11-13 23:00','2018-11-14 07:00','2018-11-13 23:00','2018-11-14 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRTHb','GfSTbS','2018-11-27','2018-11-27 23:00','2018-11-28 07:00','2018-11-27 23:00','2018-11-28 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRTHb','GfSTbS','2018-12-04','2018-12-04 23:00','2018-12-05 07:00','2018-12-04 23:04','2018-12-05 07:10');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-15','2018-11-15 23:00','2018-11-16 07:00','2018-11-15 23:00','2018-11-16 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-14','2018-11-14 23:00','2018-11-15 07:00','2018-11-14 23:00','2018-11-15 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-12','2018-11-12 23:00','2018-11-13 07:00','2018-11-12 23:00','2018-11-13 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-11','2018-11-11 23:15','2018-11-12 07:00','2018-11-11 23:15','2018-11-12 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-29','2018-11-29 23:00','2018-11-30 07:00','2018-11-29 23:00','2018-11-30 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-28','2018-11-28 23:00','2018-11-29 07:00','2018-11-28 23:00','2018-11-29 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-09','2018-12-09 23:00','2018-12-10 07:00','2018-12-09 23:00','2018-12-10 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-02','2018-12-02 23:00','2018-12-03 07:00','2018-12-02 23:00','2018-12-03 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-26','2018-11-26 23:00','2018-11-27 07:00','2018-11-26 23:00','2018-11-27 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-03','2018-12-03 23:00','2018-12-04 07:00','2018-12-03 23:00','2018-12-04 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-25','2018-11-25 23:00','2018-11-26 07:00','2018-11-25 23:00','2018-11-26 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-10','2018-12-10 22:00','2018-12-11 06:00',NULL,NULL);
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-06','2018-12-06 23:00','2018-12-07 07:00','2018-12-06 22:55','2018-12-07 07:15');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-12-05','2018-12-05 23:00','2018-12-06 07:00','2018-12-05 23:00','2018-12-06 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-22','2018-11-22 23:00','2018-11-23 07:00','2018-11-22 23:00','2018-11-23 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-21','2018-11-21 23:00','2018-11-22 07:00','2018-11-21 23:00','2018-11-22 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-19','2018-11-19 23:00','2018-11-20 07:00','2018-11-19 23:00','2018-11-20 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','MbRdc','CcDedT','2018-11-18','2018-11-18 23:00','2018-11-19 07:00','2018-11-18 23:00','2018-11-19 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-17','2018-11-17 23:00','2018-11-18 07:00','2018-11-17 23:00','2018-11-18 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-16','2018-11-16 23:00','2018-11-17 07:00','2018-11-16 23:00','2018-11-17 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-23','2018-11-23 23:00','2018-11-24 07:00','2018-11-23 23:00','2018-11-24 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-30','2018-11-30 23:00','2018-12-01 07:00','2018-11-30 23:00','2018-12-01 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-12-01','2018-12-01 23:00','2018-12-02 07:00','2018-12-01 23:00','2018-12-02 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-24','2018-11-24 23:00','2018-11-25 07:00','2018-11-24 23:00','2018-11-25 07:00');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-12-07','2018-12-07 23:00','2018-12-08 07:00','2018-12-07 22:53','2018-12-08 07:06');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-12-08','2018-12-08 23:00','2018-12-09 07:00','2018-12-08 22:55','2018-12-09 07:06');
INSERT INTO V_TestHHASchedule(Patients_FirstName,Patients_LastName,Caregivers_FirstName,Caregivers_LastName,Schedule_VisitDate,Schedule_ScheduleStartTime,Schedule_ScheduleEndTime,Schedule_VisitStartTime,Schedule_VisitEndTime) VALUES ('NbdHb','bSGHbR','THbPb-RbfT','SfSHdLb','2018-11-10','2018-11-10 23:00','2018-11-11 07:00','2018-11-10 23:00','2018-11-11 07:00');


CREATE TABLE VisitReport(
   PatientsName VARCHAR(31) NOT NULL
  ,AideName     VARCHAR(26) NOT NULL
  ,VisitDate    DATE  NOT NULL
  ,ScheduleTime VARCHAR(9) NOT NULL
  ,VisitTime    VARCHAR(9) NOT NULL
  ,Duration     VARCHAR(5) NOT NULL
);
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('RcdD DdLLbN (WGC-901974)','KdNG THcRcSb (4102)','2018-11-10','1500-2200','1500-2200','07:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('RbMSbRbN DcVdN (WGC-901975)','BgCKNeL SHcRRYLL (8806)','2018-11-10','0900-2100','0900-2100','12:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('KcLLcRMbN YbbKfV (WGC-901976)','bSeMbNdNG b PcbRL (4297)','2018-11-10','0730-0830','0730-0830','01:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('TfRfPcRcZ YbZMdN (WGC-901979)','McRdVdL MbRdc (4539)','2018-11-10','0700-2300','0700-2300','16:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('GffDLdNG CHbNCc (WGC-901980)','GcRbRDdNe G bBdGbdL (5413)','2018-11-10','0800-1600','0800-1600','08:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('KcLLcRMbN YcHfSHgb (WGC-901977)','bSeMbNdNG b PcbRL (4297)','2018-11-10','0730-0830','0730-0830','01:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('KcLLcRMbN YcHfSHgb (WGC-901977)','bSeMbNdNG b PcbRL (4297)','2018-11-10','0000-0730','0000-0730','07:30');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('SMdTH SbHYgRdc (WGC-901985)','THeMPSeN c JeY (4703)','2018-11-10','0830-1530','0830-1530','07:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('SHbSHf LdNDb (WGC-901986)','PHdLLdP C SHbReN (4007)','2018-11-10','0945-1745','0945-1745','08:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('SHbSHf LdNDb (WGC-901986)','GdLLcS MbRdc (9857)','2018-11-10','2200-0600','2200-0600','08:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('MYVcTT DbVdD (WGC-901988)','MbTTHcW Y KcLSY (4394)','2018-11-10','2200-0730','2200-0730','09:30');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('bSGHbR NbdHb (WGC-901995)','THbPb-RbgT SgSHdLb (3997)','2018-11-10','2300-0700','2300-0700','08:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('CbMPBcLL GbRY (WGC-901997)','CRdSeSTeMe MbRGdc (4285)','2018-11-10','0615-0800','0615-0800','01:45');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('CbMPBcLL GbRY (WGC-901997)','bRcLLbNe MbRY JbNc (4066)','2018-11-10','1200-2000','1200-2000','08:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('CbMPBcLL GbRY (WGC-901997)','CRdSeSTeMe MbRGdc (4285)','2018-11-10','2000-0800','2000-0800','12:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('CbMPBcLL GbRY (WGC-901997)','CRdSeSTeMe MbRGdc (4285)','2018-11-10','0800-1200','0800-1200','04:00');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('LfWY PcbRL (WGC-901998)','GRbY bMeRLY (4267)','2018-11-10','2015-0800','2015-0800','11:45');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('LfWY PcbRL (WGC-901998)','HbNLcY JbCQgcLdNc (6405)','2018-11-10','0800-2015','0800-2015','12:15');
INSERT INTO VisitReport(PatientsName,AideName,VisitDate,ScheduleTime,VisitTime,Duration) VALUES ('SdNGH PffJb (WGC-902000)','DcWbR bYbNNb (7488)','2018-11-10','1400-2200','1400-2200','08:00');

SELECT
    'join' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime, r.PatientsName, r.VisitDate, r.VisitTime
FROM V_TestHHASchedule v
inner join VisitReport r
    ON r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime 
;

SELECT
    'exists' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )  
;

SELECT DISTINCT
    'not exists' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Open in new window

Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi Paul,

Your query did not include aide name, tried adding it like below and all records are coming up

SELECT DISTINCT
    'not exists' qry, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientName LIKE v.Patients_LastName + '%'
    AND r.PatientName LIKE '%' + v.Patients_LastName + '%'
and r.AideName like  v.Caregivers_LastName + '%'
and r.AideName like '%' + v.Caregivers_LastName    
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
         
    
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Open in new window


Perhaps because of Middle name?

Please let me know how to fix them.

Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
It is just a repeat of the logic used for patient name, like this

    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'

but as you add more matching conditions you increase the likelihood of unmatched rows (e.g. there are no matched rows in the sample because the aidename is slightly different)  ... THbPb-RbfT <> THbPb-RbgT
+---+------+---------------------------+---------------------+----------------------+-------------------+--------------------+---------------------+----------------------------+---------------------------+---------------------+-----------+
|   | qry  |         AideName          | Caregivers_LastName | Caregivers_FirstName | Patients_LastName | Patients_FirstName | Schedule_VisitDate  | Schedule_ScheduleStartTime |       PatientsName        |      VisitDate      | VisitTime |
+---+------+---------------------------+---------------------+----------------------+-------------------+--------------------+---------------------+----------------------------+---------------------------+---------------------+-----------+
| 1 | join | THbPb-RbgT SgSHdLb (3997) | SfSHdLb             | THbPb-RbfT           | bSGHbR            | NbdHb              | 10.11.2018 00:00:00 | 10.11.2018 23:00:00        | bSGHbR NbdHb (WGC-901995) | 10.11.2018 00:00:00 | 2300-0700 |
+---+------+---------------------------+---------------------+----------------------+-------------------+--------------------+---------------------+----------------------------+---------------------------+---------------------+-----------+

Open in new window


SELECT
    'join' qry, AideName, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime, r.PatientsName, r.VisitDate, r.VisitTime
FROM V_TestHHASchedule v
inner join VisitReport r
    ON r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime 
;

SELECT
    'exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )  
;

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Open in new window

Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi,
It is just a repeat of the logic used for patient name, like this
This is what I mentioned above
tried adding it like below and all records are coming up
So the problem with that is, all records of table are showing up, which of course does not make sense...
Thanks,
Ben
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi Paul,
Please dont give up on this...
Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
records of table are showing up, which of course does not make sense.
It does make sense I'm afraid.

As i displayed. Clearly. The data does NOT match
         THbPb-RbfT <> THbPb-RbgT

Second last letters f does not match g

Its not me that is giving up, i can only deal with the data supplied.  I have demonstrated how matching can work, it is now up to you, with the real data at your disposal, to apply the techniques to that data.

latest: https://rextester.com/FXIAB78332
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Try using LTRIM() and/or RTRIM()  to remove invisible "whitepace"?

e.g.

    AND r.AideName LIKE RTRIM(v.Caregivers_FirstName) + '%'
    AND r.AideName LIKE '%' + RTRIM(v.Caregivers_LastName) + '%'
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi,
Tried both (to all fields), same thing.
noticed - visit start time and visit end time are two different dates, does this matter?
Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
The comparison of dates is:

cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)

times are compared only via a string:

    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
Avatar of PortletPaul
PortletPaul
Flag of Australia image
ADD ONE restriction at a time, e.g.


SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
 

order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
 

order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;


SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'

order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)

order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

mix it up a bit as well

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'
    AND r.PatientsName LIKE '%' + v.Patients_LastName + '%'
    AND r.AideName LIKE v.Caregivers_FirstName + '%'
    AND r.AideName LIKE '%' + v.Caregivers_LastName + '%'
    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE r.PatientsName LIKE v.Patients_LastName + '%'

    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Wok out where the problem lies, then concentrate on the problem.

The overall problem is DATA
With no data to work with I have zero probability of guessing what this problem is now.
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Hi Paul,

Here is what I realized.

When using the following for name comparison I do get a relative smaller amount of records (including that record that is gone..)
         ltrim(r.AideName) LIKE  '%' + ltrim(v.Caregivers_FirstName) + '%'
    AND ltrim(r.AideName) LIKE '%' + ltrim(v.Caregivers_LastName) + '%'
    and ltrim(r.PatientName) LIKE '%'+ ltrim(v.Patients_LastName) + '%'
    AND ltrim(r.PatientName) LIKE '%' + ltrim(v.Patients_firstName) + '%'

Open in new window


Also realized that the following is not returning any value in some cases (see attached).
replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','') as VisitTime

Open in new window

I believe once we clear that up, everything should move smoothly.

Thanks,
Ben
Untitled.png
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Actually I stayed with the following

SELECT DISTINCT
    'not exists' qry, Caregivers_LastName, Caregivers_FirstName, Patients_LastName, Patients_FirstName, Schedule_VisitDate,
     Schedule_ScheduleStartTime, v.Schedule_ScheduleEndTime,
    replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','') as VisitTime
FROM V_TestHHASchedule v
WHERE NOT EXISTS (
    SELECT
        NULL
    FROM VisitReport r
    WHERE 
         ltrim(r.AideName) LIKE  '%' + ltrim(v.Caregivers_FirstName) + '%'
    AND ltrim(r.AideName) LIKE '%' + ltrim(v.Caregivers_LastName) + '%'
    and ltrim(r.PatientName) LIKE '%'+ ltrim(v.Patients_LastName) + '%'
    AND ltrim(r.PatientName) LIKE '%' + ltrim(v.Patients_firstName) + '%'

    AND  cast(v.Schedule_VisitDate as date)     = cast(r.VisitDate as date)
    AND ( replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')
          + '-'
          + replace(convert(varchar(5),v.Schedule_VisitEndTime,108),':','')) = r.VisitTime
    )
and (replace(convert(varchar(5),v.Schedule_VisitStartTime,108),':','')) is not null
and  (replace(convert(varchar(5),v.Schedule_VisitendTime,108),':','')) is not null
order by Patients_LastName, Patients_FirstName, Schedule_VisitDate, Schedule_ScheduleStartTime
;

Open in new window


Just wondering if we add a check for middle names in name comparison if it would add to the quantity of none matching records.

Thanks,
Ben
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Just wondering if we add a check for middle names in name comparison if it would add to the quantity of none matching records.

Against what would you compare it?
e.g. This does not contain a middle name: ('RcdD DdLLbN (WGC-901974)'

i.e. it seems to be in only one of the 2 sets of data, so it cannot be compared.

----
Also realized that the following is not returning any value in some cases
Why?????
Are either of the date columns NULL???
can you provide some rows of data that do that (not image, but just the date columns)
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs
bfuchs
Flag of United States of America image

ASKER

Good catch Paul, thank you!!