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