I'm on SQL Server 2014. I have a table with columns like this:

I need to select rows where the current date/time (GETDATE()) lands within the day-of-week/time range represented by these datetimes, beginning on the effectiveDate. So think of row 1 as the range Wednesday 18:00 -> Wednesday 23:00 and row 2 as Friday 22:00 -> Saturday 02:00. (Keep in mind the day-of-week/time range can span multiple days, as in the 2nd row.)

Examples:

• GETDATE() = 2022-07-06 19:30:00.000 (i.e Wednesday at 19:30)
  • Selects row 1
• GETDATE() = 2022-07-30 01:00:00.000 (i.e. Saturday at 01:00)
  • Selects row 2
• GETDATE() = 2022-06-30 19:00:00.000 (i.e. Wednesday at 19:00 which matches row 1 on the day-of-week/time range, but is before the effective date)
  • Selects nothing

I'm not quite sure how to attack this. Maybe we could date adjust each row's effectiveDate and expirationDate as many weeks forward as needed to place the effectiveDate before GETDATE() (assuming the effectiveDate <= GETDATE()). Any thoughts?

CodePudding user response：

The DATEPART with weekday is the key to checking the weekday range. There are two cases. For example, Mon to Wed is different than Wed to Mon. Mon to Wed is easy with a test of Mon<=day<= Wed. For Wed to Mon, it becomes day < Mon or day > Wed. However, this is the same as NOT Mon<=day<=Wed. The query might not be exactly what you need, but should be a good start.

with TestData as (
    select * 
    from (
        values 
            (1, CAST('2022-07-06 18:00:00.000' as datetime), CAST('2022-07-06 23:00:00.000' as datetime)),
            (2, CAST('2022-07-08 22:00:00.000' as datetime), CAST('2022-07-09 02:00:00.000' as datetime)),
            (3, CAST('2022-07-09 22:00:00.000' as datetime), CAST('2022-07-11 10:00:00.000' as datetime))
    ) t (id, effectiveDate, expirationDate)
), TestSamples as (
    select * 
    from (
        values 
            (CAST('2022-07-06 19:30:00.000' as datetime)),
            (CAST('2022-07-30 01:00:00.000' as datetime)),
            (CAST('2022-07-31 01:00:00.000' as datetime)),
            (CAST('2022-06-30 19:00:00.000' as datetime)),
            (CAST('2022-08-04 19:00:00.000' as datetime))
    ) t (testDate)
), WeekDayRange as (
    select id, effectiveDate, expirationDate, 
        datename(weekday, effectiveDate) as WDNAME1, datename(weekday, expirationDate) as WDNAME2, 
        datepart(weekday, effectiveDate) as WD1, datepart(weekday, expirationDate) as WD2
    from TestData
) select t.testDate, datename(weekday, t.testDate) as [WDNAME], datepart(weekday, t.testDate) as [WD], r.*
from  TestSamples t
left join WeekDayRange r
on (r.WD1 <= r.WD2 and datepart(weekday, t.testDate) BETWEEN r.WD1 and r.WD2)
    or (r.WD1 > r.WD2 and not datepart(weekday, t.testDate) BETWEEN r.WD2 and r.WD1)
where t.testDate > r.effectiveDate

testDate                WDNAME    WD          id          effectiveDate           expirationDate          WDNAME1   WDNAME2   WD1         WD2
----------------------- --------- ----------- ----------- ----------------------- ----------------------- --------- --------- ----------- -----------
2022-07-06 19:30:00.000 Wednesday 4           1           2022-07-06 18:00:00.000 2022-07-06 23:00:00.000 Wednesday Wednesday 4           4
2022-07-30 01:00:00.000 Saturday  7           2           2022-07-08 22:00:00.000 2022-07-09 02:00:00.000 Friday    Saturday  6           7
2022-07-31 01:00:00.000 Sunday    1           3           2022-07-09 22:00:00.000 2022-07-11 10:00:00.000 Saturday  Monday    7           2

CodePudding user response：

I think I got this working with a bit of math:

DECLARE @myGetDate DATETIME;
SET @myGetDate = '2022-07-30 01:00:00.000';

WITH AdjustedWeeklyDates AS (
    SELECT
        ID,
        DATEADD(WEEK, DATEDIFF(day, effectiveDate, @myGetDate) / 7, effectiveDate) AS adjustedEffectiveDate,
        DATEADD(WEEK, DATEDIFF(day, effectiveDate, @myGetDate) / 7, expirationDate) AS adjustedExpirationDate
    FROM
        dbo.myTable
    WHERE
        effectiveDate <= @myGetDate
)
SELECT
    mt.ID, mt.effectiveDate, mt.expirationDate, awd.adjustedEffectiveDate, awd.adjustedExpirationDate
FROM dbo.myTable mt
    INNER JOIN AdjustedWeeklyDates awd ON mt.ID = awd.ID
WHERE 
    awd.adjustedEffectiveDate <= @myGetDate
    AND awd.adjustedExpirationDate > @myGetDate

To explain:

• DATEDIFF(day, effectiveDate, @myGetDate) returns the number of days between the effectiveDate and the current date. So for example, say it was 20 days ago.
• / 7 gets the number of weeks as an int, since the DATEDIFF returns an int. This also results in the floor of the quotient. So, with our example 20 days / 7, the quotient is about 2.86, but this will result in an even 2
• DATEADD adds the number of weeks to bring us up to or before the current date/time. We add the same number of weeks to the expiration date, resulting in the same range as the original effective/expiration dates, which may or may not extend around the current date.
• Finally the check for effectiveDate <= @myGetDate guarantees the current date is after or equal to the effective date.