sp_QuickieStore - Add a workday mode

[erikdarlingdata]

I would like to add a @workday mode that would add some additional filters to the where clause that (to start) would filter out weekends and "after hours" activity.

The general way it would work is:

• If @@DATEFIRST returns 1, it will filter DATEPART(WEEKDAY, SYSDATETIME()) 1-5
• If @@DATEFIRST returns 7, it will filter DATEPART(WEEKDAY, SYSDATETIME()) 2-6

The next thing I have to do is build a table of times to set up the start and end hours, let's call them @work_start and @work_end:

SELECT
    am_pm = 
        CASE
            WHEN y.t24 BETWEEN 1 AND 11
            THEN RTRIM(y.t12) + 'am'
            WHEN y.t24 = 0
            THEN RTRIM(y.t12) + 'am'
            ELSE RTRIM(y.t12) + 'pm'
        END,
    y.t12,
    y.t24
FROM
(
    SELECT
        t12 = 
            CASE x.t12
                 WHEN 0
                 THEN 12
                 ELSE x.t12
            END,
        t24 = 
            CASE 
                WHEN x.t24 < 24
                THEN x.t24
                ELSE 0
            END
    FROM
    (
        SELECT TOP (24)
            t12 = 
                ROW_NUMBER() OVER
                (
                    ORDER BY
                        1/0
                ) % 12,
            t24 = 
                ROW_NUMBER() OVER
                (
                    ORDER BY
                        1/0
                )
        FROM sys.messages AS m
    ) AS x
) AS y
ORDER BY
    y.t24;

An example execution would look something like:

EXEC sp_QuickieStore
    @workdays = 'true',
    @work_start = '9am',
    @work_end = '5pm';

Which would translate to (for me):

DATEPART(WEEKDAY, SYSDATETIME()) BETWEEN 2 AND 6
DATEPART(HOUR, SYSDATETIME()) BETWEEN 9 AND 17

Okay cool. Merry Christmas.

[erikdarlingdata]

Here is some proto code that seems to work for what I want it to do.

SET NOCOUNT ON;

/*Dingus*/
DROP TABLE IF EXISTS
    #am_pm;
GO

/*These will be parameters*/
DECLARE
    @workdays bit = 'true',
    @work_start varchar(4) = '9am',
    @work_end varchar(4) = '5pm';

/*These will be variables*/
DECLARE
    @df integer = @@DATEFIRST,
    @work_start_int integer = 0,
    @work_end_int integer = 0,
    @sql nvarchar(MAX) = N'';

SELECT
    @sql += N'
SELECT
    dpw = DATEPART(WEEKDAY, qsrs.last_execution_time),
    dph = DATEPART(HOUR, qsrs.last_execution_time),
    qsrs.*
FROM StackOverflow2013.sys.query_store_runtime_stats AS qsrs
WHERE 1 = 1 ';

IF @workdays = 'true'
BEGIN
    SELECT
        @work_start = LOWER(REPLACE(@work_start, ' ', '')),
        @work_end   = LOWER(REPLACE(@work_end, ' ', ''));
    
    SELECT
        am_pm = 
            CASE
                WHEN y.t24 BETWEEN 1 AND 11
                THEN RTRIM(y.t12) + 'am'
                WHEN y.t24 = 0
                THEN RTRIM(y.t12) + 'am'
                ELSE RTRIM(y.t12) + 'pm'
            END,
        y.t12,
        y.t24
    INTO #am_pm
    FROM
    (
        SELECT
            t12 = 
                CASE x.t12
                     WHEN 0
                     THEN 12
                     ELSE x.t12
                END,
            t24 = 
                CASE 
                    WHEN x.t24 < 24
                    THEN x.t24
                    ELSE 0
                END
        FROM
        (
            SELECT TOP (24)
                t12 = 
                    ROW_NUMBER() OVER
                    (
                        ORDER BY
                            1/0
                    ) % 12,
                t24 = 
                    ROW_NUMBER() OVER
                    (
                        ORDER BY
                            1/0
                    )
            FROM sys.messages AS m
        ) AS x
    ) AS y
    ORDER BY
        y.t24;
    
    SELECT
        @work_start_int =
        (
            SELECT
                ap.t24
            FROM #am_pm AS ap
            WHERE ap.am_pm = @work_start
        ),
        @work_end_int =
        (
            SELECT
                ap.t24
            FROM #am_pm AS ap
            WHERE ap.am_pm = @work_end
        );
    
    IF  @work_start_int IS NULL
    AND @work_end_int   IS NULL
    BEGIN
         SELECT
             @work_start_int = 9,
             @work_end_int = 17;
    END;
    
    IF  @work_start_int IS NOT NULL
    AND @work_end_int   IS NULL
    BEGIN
        SELECT
            @work_end_int = @work_start_int + 12;
    END;
    
    IF  @work_start_int IS NULL
    AND @work_end_int   IS NOT NULL
    BEGIN
        SELECT
            @work_start_int = @work_end_int - 12;
    END;

    SELECT
        @work_start_int += 
            DATEDIFF
            (
                MINUTE,
                SYSDATETIME(),
                SYSUTCDATETIME()
            ) / 60,
        @work_end_int += 
            DATEDIFF
            (
                MINUTE,
                SYSDATETIME(),
                SYSUTCDATETIME()
            ) / 60;
 
    IF @df = 1
    BEGIN
           SELECT
               @sql += N'
AND  DATEPART(WEEKDAY, qsrs.last_execution_time) BETWEEN 1 AND 6';
    END;/*df 1*/
    
    IF @df = 7
    BEGIN
           SELECT
               @sql += N'
AND  DATEPART(WEEKDAY, qsrs.last_execution_time) BETWEEN 2 AND 6';
    END;/*df 2*/

    IF  @work_start_int IS NOT NULL
    AND @work_end_int IS NOT NULL
    BEGIN
        SELECT
            @sql += N'
AND  DATEPART(HOUR, qsrs.last_execution_time) BETWEEN @work_start_int AND @work_end_int';
    END; /*Work hours*/

    SELECT
        @sql += N'
ORDER BY
    dpw,
    dph;';

    RAISERROR(@sql, 0, 1) WITH NOWAIT;

    EXEC sys.sp_executesql
        @sql,
      N'@work_start_int integer,
        @work_end_int integer',
        @work_start_int,
        @work_end_int;
END; /*Final end*/