Here's one I prepared earlier:
DECLARE @Lab_Time DateTime,
@StartDate_Yesterday DateTime,
@Two_AM_Yesterday DateTime,
@EndDate_Yesterday DateTime;
-- Set date values:
-- Use >= & < to check dates - this way you don't miss any & you don't count any twice!
SELECT @Lab_Time = CONVERT(VARCHAR(30),GETDATE()-1,101)+' 07:00:00 AM',
@StartDate_Yesterday = DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE())),
@Two_AM_Yesterday = CONVERT(DATETIME,ltrim(CONVERT(VARCHAR(30),GETDATE() -1,101))+' 02:00:00 AM'),
@EndDate_Yesterday = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));
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
(
-- Yesterday midnight - 2 a.m.
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, @Lab_Time) 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 >= @StartDate_Yesterday
AND dbo.BVActions.orderedTime < @Two_AM_Yesterday)
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
(
-- Yesterday 2 a.m. onwards
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, @Lab_Time) 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 >= @Two_AM_Yesterday
AND dbo.BVActions.orderedTime < @EndDate_Yesterday)
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