If today is Monday show Friday to Sunday

  • Hi Guys,  I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday.  Is this possible?

    Thanks

    DECLARE @sd DATETIME;

    DECLARE @ed DATETIME;

    -- set the start date yesterday

    SET @sd = dateadd(day,datediff(day,1,GETDATE()),0) 
    -- set end dates - today

    SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);


  • craig.jenkins - Thursday, May 24, 2018 9:59 AM

    Hi Guys,  I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday.  Is this possible?

    Thanks

    DECLARE @sd DATETIME;

    DECLARE @ed DATETIME;

    -- set the start date yesterday

    SET @sd = dateadd(day,datediff(day,1,GETDATE()),0) 
    -- set end dates - today

    SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);


    Try this:-- set the start date yesterday or Friday if yesterday is Sunday
    DECLARE @sd AS datetime =
        CASE DATEPART(weekday, DATEADD(day, -1, GETDATE()))
            WHEN 1 THEN CONVERT(date, DATEADD(day, -3, GETDATE()))
            ELSE CONVERT(date, DATEADD(day, -1, GETDATE()))
        END;

    -- set end dates - today
    DECLARE @ed AS datetime = CONVERT(date, GETDATE());

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • Here you go. Use a CASE. Also, use DATETIME2 and SYSDATETIME(). Always assign a date variable, then use the current date as a default. This let's you test, and reuse the code for other purposes.

    DECLARE @sd DATETIME2(3) ,
       @ed DATETIME2(3) ,
       @mydate DATETIME2(3) = SYSDATETIME();

    SELECT @mydate = '2018-05-21';

    -- set the start date yesterday

    -- set end dates - today
    SET @sd = CASE
         WHEN DATENAME(dw, @mydate) = 'Monday' THEN
          DATEADD( DAY, -3, DATEADD(DAY, DATEDIFF(DAY, 0, @mydate), 0))
         ELSE
          DATEADD(DAY, DATEDIFF(DAY, 1, @mydate), 0)
        END;

    SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, @mydate), 0);

    SELECT @sd ,
       @ed;

    SELECT @sd ,   @ed;

  • Hello try this,

    WITH DATERANGE AS
    (
    SELECT DT = DATEADD(DD, 0, T.STARTDT), ET=T.ENDDATE
    FROM (SELECT
            CASE WHEN DATENAME(DW, DATEADD(DAY, 0, GETDATE())) != 'MONDAY'
                    THEN DATEADD(DAY, -1, GETDATE())
            ELSE DATEADD(DAY, -3, GETDATE()) END STARTDT,    
            CASE WHEN DATENAME(DW, DATEADD(DAY, 0, GETDATE())) != 'MONDAY'
                    THEN DATEADD(DAY, -1, GETDATE())
            ELSE DATEADD(DAY, -1, GETDATE()) END ENDDATE
     ) T
    WHERE DATEADD(DD, 0, T.STARTDT) <= T.ENDDATE
    UNION ALL
    SELECT DATEADD(DD, 1, DT), ET
    FROM DATERANGE
    WHERE DATEADD(DD, 1, DT) <= ET
    )
    SELECT DT
    FROM DATERANGE

  • craig.jenkins - Thursday, May 24, 2018 9:59 AM

    Hi Guys,  I am using the below to show data relating to yesterdays date, It works great but on a Monday I need this to show for Friday, Saturday and Sunday rather than just yesterday.  Is this possible?

    Thanks

    DECLARE @sd DATETIME;

    DECLARE @ed DATETIME;

    -- set the start date yesterday

    SET @sd = dateadd(day,datediff(day,1,GETDATE()),0) 
    -- set end dates - today

    SET @ed = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0);



    -- Set Monday as the First Day
    Set DateFirst 1;

    Declare
        @sd As Date = Case Datepart(weekday, GetDate()) when 1 then DateAdd(day,-3,GetDate()) else GetDate() end,
        @ed As Date = GetDate()

    Select @sd As StartDate, @ed As EndDate;

  • I'd be inclined to use a calendar table that includes columns to indicate the days that are likely to be excluded (whether that be weekends or a more general non-working days thing) because this type of problem almost always ends up in a change of requirements along the lines of "Oh, well when it's the Tuesday after a Bank Holiday Monday then you need to show everything since Friday" or similar.

    With a suitable table it's a relatively straightforward LAG function select to get the previous suitable day to start on.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply