I highly recommend using a Calendar Table for this. A calendar table holds date values with additional information, so it's easier to find particular days (like business days or weekdays, as for this example).
The following solution uses a calendar table and 2 CROSS APPLY operators to get the previous collection dates.
This is how you can create a calendar table (recursive CTE):
SET DATEFIRST 1 -- 1: Monday, 7: Sunday
-- Create a Calendar Table
IF OBJECT_ID('tempdb..#CalendarTable') IS NOT NULL
DROP TABLE #CalendarTable
;WITH CalendarTable AS
(
SELECT
Date = CONVERT(DATE, '2016-01-01'),
Weekday = DATEPART(WEEKDAY, '2016-01-01')
UNION ALL
SELECT
Date = DATEADD(DAY, 1, C.Date),
Weekday = DATEPART(WEEKDAY, DATEADD(DAY, 1, C.Date))
FROM
CalendarTable AS C
WHERE
C.Date <= '2020-01-01'
)
SELECT
C.Date,
C.Weekday
INTO
#CalendarTable
FROM
CalendarTable AS C
OPTION
(MAXRECURSION 0)
The table is like the following:
SELECT * FROM #CalendarTable ORDER BY Date DESC
Date Weekday
2020-01-02 4
2020-01-01 3
2019-12-31 2
2019-12-30 1
2019-12-29 7
2019-12-28 6
2019-12-27 5
2019-12-26 4
2019-12-25 3
2019-12-24 2
2019-12-23 1
2019-12-22 7
2019-12-21 6
2019-12-20 5
2019-12-19 4
2019-12-18 3
2019-12-17 2
2019-12-16 1
2019-12-15 7
2019-12-14 6
2019-12-13 5
2019-12-12 4
2019-12-11 3
We will use this to find the closest Wednesday and Monday just before a particular Departure date. We find this using a CROSS APPLY having the DepartureDate as a higher limit, then searching for the particular weekday (1 for monday, 3 for wednesday). Then use TOP 1 with ORDER BY Date DESC to get the highest Monday/Wednesday just before that departure date.
-- Build your Collect periods
;WITH SampleData AS
(
SELECT
V.Departure
FROM
(VALUES
('2018-12-01'),
('2018-12-09'),
('2018-12-25'),
('2018-12-29'),
('2019-01-02'),
('2019-01-07'),
('2019-01-10')) AS V(Departure)
)
SELECT
V.Departure,
-- Friday to Wednesday
ClosestWednesdayBeforeDeparture = W.Date,
PreviousFridayOfThatWednesday = DATEADD(DAY, -5, W.Date),
-- Wednesday to Monday
ClosestMondayBeforeDeparture = M.Date,
PreviousWednesdayOfThatMonday = DATEADD(DAY, -5, M.Date),
-- Check for odd/even
IsOdd = CASE WHEN DATEPART(DAY, V.Departure) % 2 = 1 THEN 1 ELSE 0 END,
-- Use previous expressions to build your collect periods
Collect = CASE
WHEN
DATEPART(DAY, V.Departure) % 2 = 1 -- IsOdd
THEN
CONVERT(VARCHAR(100), DATEADD(DAY, -5, W.Date), 120) -- PreviousFridayOfThatWednesday
+ ' TO '
+ CONVERT(VARCHAR(100), W.Date, 120) -- ClosestWednesdayBeforeDeparture
ELSE -- IsEven
CONVERT(VARCHAR(100), DATEADD(DAY, -5, M.Date), 120) -- PreviousWednesdayOfThatMonday
+ ' TO '
+ CONVERT(VARCHAR(100), M.Date, 120) -- ClosestMondayBeforeDeparture
END
FROM
SampleData AS V
CROSS APPLY (
SELECT TOP 1
C.Date
FROM
#CalendarTable AS C
WHERE
C.Date < V.Departure AND
C.Weekday = 3 -- 3: Wednesday
ORDER BY
C.Date DESC) AS W
CROSS APPLY (
SELECT TOP 1
C.Date
FROM
#CalendarTable AS C
WHERE
C.Date < V.Departure AND
C.Weekday = 1 -- 1: Monday
ORDER BY
C.Date DESC) AS M
ORDER BY
V.Departure
Finding the previous Friday from a Wednesday is as simple as moving backwards 5 days, the same goes from Monday to Wednesday.
Results:
Departure IsOdd Collect ClosestWednesdayBeforeDeparture PreviousFridayOfThatWednesday ClosestMondayBeforeDeparture PreviousWednesdayOfThatMonday
2018-12-01 1 2018-11-23 TO 2018-11-28 2018-11-28 2018-11-23 2018-11-26 2018-11-21
2018-12-09 1 2018-11-30 TO 2018-12-05 2018-12-05 2018-11-30 2018-12-03 2018-11-28
2018-12-25 1 2018-12-14 TO 2018-12-19 2018-12-19 2018-12-14 2018-12-24 2018-12-19
2018-12-29 1 2018-12-21 TO 2018-12-26 2018-12-26 2018-12-21 2018-12-24 2018-12-19
2019-01-02 0 2018-12-26 TO 2018-12-31 2018-12-26 2018-12-21 2018-12-31 2018-12-26
2019-01-07 1 2018-12-28 TO 2019-01-02 2019-01-02 2018-12-28 2018-12-31 2018-12-26
2019-01-10 0 2019-01-02 TO 2019-01-07 2019-01-09 2019-01-04 2019-01-07 2019-01-02
This was a good SQL exercise.
