-- Have a look at the result of this:
-- ('#' is an arbitrary divider between the columns of one table and the columns of another)
SELECT h.*, '#' '#', x.*
FROM HoursOfOperation h
OUTER APPLY (
SELECT TOP 1 hi.CalendarDate, hi.WorkingDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
ORDER BY h.BusinessUnit, h.CalendarDate;
---------------------------------------------------------
-- Here's how to apply it.
---------------------------------------------------------
-- Check that everything looks ok:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)
SELECT t.*, '#' '#', j.*, '#' '#', s.*
FROM SLHIH t
LEFT JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
LEFT JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate
ORDER BY t.BusinessUnit, t.WorkDate;
-- Screw things down a bit and check again:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)
SELECT t.*, '#' '#', j.*, '#' '#', s.*
FROM SLHIH t
INNER JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
INNER JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate
ORDER BY t.BusinessUnit, t.WorkDate;
-- Convert to an UPDATE:
WITH JoinTable AS (
SELECT h.BusinessUnit, h.CalendarDate, SwitchDate = x.CalendarDate
FROM HoursOfOperation h
CROSS APPLY (
SELECT TOP 1 hi.CalendarDate
FROM HoursOfOperation hi
WHERE hi.BusinessUnit = h.BusinessUnit
AND hi.CalendarDate < h.CalendarDate
AND hi.WorkingDate = 1
AND h.WorkingDate = 0
ORDER BY hi.CalendarDate DESC
) x
)
UPDATE t SET
TotalContacts= s.TotalContacts,
SIH= s.SIH,
SLEligible= s.SLEligible,
InServiceLevel= s.InServiceLevel
FROM SLHIH t
INNER JOIN JoinTable j
ON j.BusinessUnit = t.BusinessUnit
AND j.CalendarDate = t.WorkDate
INNER JOIN SLHIH s
ON s.BusinessUnit = j.BusinessUnit
AND s.WorkDate = j.SwitchDate;
-- with even basic indexing in place, it's pretty quick
-- CREATE UNIQUE CLUSTERED INDEX ucx_BusinessUnit_CalendarDate ON HoursOfOperation (BusinessUnit, CalendarDate)
-- CREATE UNIQUE CLUSTERED INDEX ucx_BusinessUnit_WorkDate ON SLHIH (BusinessUnit, WorkDate)
-- Don't use these!!!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden