reporting Wednesday to wednesday

user_4368856 0
Viewed 82

I am trying run a report based on a Wednesday to Wednesday reporting week. I have found lots of examples that say the get correct date, such as:

DATEADD(DAY, -5, DATEADD(WEEK, DATEDIFF(WEEK, 0, getdate()), 0))

The problem is that these seem to only work for the current week. The example above gives 15/11/17 which is correct for today (24/11/2017) but incorrect on Sunday (26/11/2017) when it would get the 22/11/2017.

if I look at the report between 22-28/11/2017(Wed-Tue) I should see a report from 15-22/11/2017

Between the 29/11 & 5/12/2017 the report should be from 22-29/11/2017

any help is appreciated!

sql-server sql
4 Answers

Try this method

SELECT
  *,
  CASE WHEN [wensday]>reportDate THEN DATEADD(WEEK,-2,[wensday]) ELSE DATEADD(WEEK,-1,[wensday]) END [from date],
  CASE WHEN [wensday]>reportDate THEN DATEADD(WEEK,-1,[wensday]) ELSE [wensday] END [to date]
FROM
  (
    SELECT
      reportDate,
      -- monday of this week
      --DATEADD(DAY,DATEDIFF(DAY,0,reportDate)/7*7,0) [monday],
      -- wensday of this week
      DATEADD(DAY,2,DATEADD(DAY,DATEDIFF(DAY,0,reportDate)/7*7,0)) [wensday]
    FROM
      (
        SELECT CAST('20171115' AS date) reportDate
        UNION ALL
        SELECT CAST('20171121' AS date) reportDate
        UNION ALL
        SELECT CAST('20171122' AS date) reportDate
        UNION ALL
        SELECT CAST('20171123' AS date) reportDate
        UNION ALL
        SELECT CAST('20171124' AS date) reportDate
        UNION ALL
        SELECT CAST('20171129' AS date) reportDate
        UNION ALL
        SELECT CAST('20171205' AS date) reportDate
      ) test
  ) q

The inline algorithm

DECLARE @reportDate date=GETDATE()

DECLARE @Wensday date=DATEADD(DAY,2,DATEADD(DAY,DATEDIFF(DAY,0,@reportDate)/7*7,0))
DECLARE @FromDate date=CASE WHEN @Wensday>@reportDate THEN DATEADD(WEEK,-2,@Wensday) ELSE DATEADD(WEEK,-1,@Wensday) END
DECLARE @ToDate date=DATEADD(WEEK,1,@FromDate)

SELECT @reportDate,@Wensday,@FromDate,@ToDate
user_89314500

You can use DATEFIRST to assign days 1-7 to Wed - Tue

Whatever date you put in here, it will return the first prior Wednesday

The following Tuesday is just 6 days after that.

-- Wednesday will be 1 when we use datepart dw
SET DATEFIRST 3;

DECLARE @ReportDate DATE
SET @ReportDate = '2017-11-26'


SELECT 
DATEADD(    d,
            -(DATEPART(dw,@ReportDate)-1),
            @ReportDate
        )
user_16901930

Try this...

DECLARE @fDt as date,@fDtR as date
set @fDt = '11/15/2017'
SET DATEFIRST 3
SELECT @fDtR = DATEADD(d,-6-(DATEPART(dw,@fDt)),@fDt)
print @fDtR
user_39210500

you can try this

select * from youtablename where yourdatefield <= 
 dateadd(dd,datediff(dd,0,getdate())/7 * 7 + 2,0)) and yourdatefield > 
 dateadd(dd,datediff(dd,0,getdate())/7 * 7 + 2,-7))

From this you will get last wednesday

select 
 dateadd(dd,datediff(dd,0,getdate())/7 * 7 + 2,0))

and from this you will get last to last wednesday

select dateadd(dd,datediff(dd,0,getdate())/7 * 7 + 2,-7))

so that you can find Wednesday to Wednesday data.

user_68478060
Related