If I understand, you are getting 2 dates for some patients, & you only want one.
You want to choose which to display when there are 2.
You can join the 2 result sets & keep whichever single one appears, or choose where there are 2 - this always keeps A.
If you want something else let me know & I'll look into it.
The basic layout would be like this:
SELECT ISNULL(A.Patients_Name, B.Patients_Name) as Patients_Name,
ISNULL(A.MR#, B.MR#) as MR#,
ISNULL(A.Required_Lab_Test, B.Required_Lab_Test) as Required_Lab_Test,
ISNULL(A.Lab_Time, B.Lab_Time) as Lab_Time,
ISNULL(A.Location_Name, B.Location_Name) as Location_Name
FROM
(
Query1 A
)
full outer join
(
Query2 B
)
ON A.key = B.key
SELECT ISNULL(A.Patients_Name, B.Patients_Name) as Patients_Name,
ISNULL(A.MR#, B.MR#) as MR#,
ISNULL(A.Required_Lab_Test, B.Required_Lab_Test) as Required_Lab_Test,
ISNULL(A.Lab_Time, B.Lab_Time) as Lab_Time,
ISNULL(A.Location_Name, B.Location_Name) as Location_Name
FROM
(
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE()-1,101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '00:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE() -1,101))+'02:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) A
FULL OUTER JOIN
(
SELECT
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName) AS Patients_Name ,
(dbo.MO_Demographics.MRN) AS MR#,
(dbo.BVActions.[IPR_Display]) AS Required_Lab_Test,
ISNULL(dbo.BVActions.scheduleTime,CONVERT(CHAR(30),GETDATE(),101)+'07:00:00 AM') AS Lab_Time,
(dbo.bllocation.[location_name]) AS Location_Name
FROM dbo.MO_Demographics LEFT OUTER JOIN dbo.BLSession
ON dbo.MO_Demographics.SessionID = dbo.BLSession.SessionID
INNER JOIN dbo.BLPatient_Location
ON dbo.MO_Demographics.SessionID = dbo.BLPatient_Location.SessionID
LEFT OUTER JOIN dbo.BVActions
ON dbo.MO_Demographics.SessionID = dbo.BVActions.SessionID
LEFT OUTER JOIN dbo.bllocation
ON dbo.bllocation.location_num = dbo.BLPatient_Location.location_num
--INNER JOIN dbo.BLLocation_Group
--ON dbo.bllocation.FacilityID = dbo.BLLocation_Group.FacilityID
WHERE dbo.BVActions.orderedTime
BETWEEN CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '02:00:01 AM')
AND CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'00:00:00 AM')
AND dbo.BVActions.[ForEveryText] = ' (Collect: Lab)'
AND dbo.BVActions.[scheduleString] = ('at AM PostPartum day 1')
AND dbo.BVActions.[cancelledSig] IS NULL
AND dbo.bllocation.[location_name] IS NOT NULL
AND dbo.MO_Demographics.MRN IS NOT NULL
AND dbo.BLPatient_Location.exit_time IS NULL
AND dbo.BVActions.[cancelledSig] IS NULL
GROUP BY
(dbo.MO_Demographics.LastName + ' ' + dbo.MO_Demographics.FirstName),
(dbo.MO_Demographics.MRN),
dbo.BVActions.[IPR_Display],
(dbo.BVActions.scheduleTime),
(dbo.bllocation.[location_name])
) B
ONA.Patients_Name = B.Patients_Name