If today is Monday show Friday to Sunday

• craig.jenkins

  SSCommitted

  Points: 1643

  More actions

  May 24, 2018 at 9:59 am

  #347963

  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);

  
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  May 24, 2018 at 10:23 am

  #1991844

  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)

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  May 24, 2018 at 10:45 am

  #1991858

  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;

• prasannaj 13253

  Old Hand

  Points: 332

  More actions

  May 28, 2018 at 7:54 am

  #1992112

  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

• neels 2599

  SSC Rookie

  Points: 33

  More actions

  May 29, 2018 at 4:49 am

  #1992150

  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;

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  May 31, 2018 at 3:45 pm

  #1992572

  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.