Turning week number into week commencing in SSRS/SQL Server

Question

I have a report that is grouped on week number but for presentation reasons want it to be week commencing.

    Select
        datepart(wk,[rhStartTime]) as [week number]

        ...

  group by datepart(wk,[rhStartTime]),[rhOperatorName])
      where 
    [week number] >= @StartWeek 
  and [week number] <= @EndWeek

My report parameters use week number to filter the data with @StartWeek and @EndWeek being integers that plug into the SQL. My question is one of presentation. It is tough for users to understand what Week 15 means in context so I would like to alter my output to show Week Commencing rather than week number but for the backend to still use weeknumber. I also don't want users to be able to pick any date because they will invariably pick dates that span multiple weeks without a full weeks data.

I look at similar questions and one here SO question recommended SQL of the format

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]

But plugging my columns into that format gave me a bit of a mess. It didn't group how I was expecting.

SELECT 

DATEADD(dd, -(datepart(wk,[rhStartTime]))-1), [rhStartTime])) as [week commencing]

      ,datepart(wk,[rhStartTime])) as [week number]

...

group by datepart(wk,[rhStartTime])),DATEADD(dd, -(datepart(wk,[rhStartTime]))-1), [rhStartTime])),[rhoperatorname]

I got this output

where I was looking for all those week 15s to be grouped together with just one week commencing date.

Please add some representative test data and your desired output from that test data. Showing us your query and the incorrect result it produces isn't all that helpful for us. — iamdave, Jun 13 '17 at 09:49

score 0 · Answer 1 · answered Jun 12 '17 at 11:04

0

Try This will work.This retrieves the dates eliminating time part of it

SELECT 

Dateadd(dd,-(datepart(wk,convert( varchar(10),[rhStart Time],120))-1), convert( varchar(10),[rhStart Time],120))
 ,datepart(wk,[rhStart Time])) as [week number]
...
from Table X
 group by Dateadd(dd,-(datepart(wk,convert( varchar(10),[rhStart Time],120))-1), convert( varchar(10),[rhStart Time],120))
          ,datepart(wk,[rhStart Time]))
          ,[Agent Name]

answered Jun 12 '17 at 11:04

Ven

2,011
1
13
27

This answer seems to suffer from the same issue. The Dateadd(... column has multiple rows for each [week number] rather than one that I'm looking for. – tomdemaine Jun 12 '17 at 11:21
can you edit your question and include a script to reproduce a sample of your data, we can then use that to test against. – Alan Schofield Jun 12 '17 at 16:40

score 0 · Answer 2 · answered Jun 13 '17 at 09:57

I think your problem is in how you are using the examples you have seen elsewhere and not with the examples themselves, as I have just tested the logic and it seems to be working for me without issue, as you can see in the script below.

I think your main problem is that you are not removing the time portion of your StartTime values, which you will need to do if you want to group all values that occur on the same day. The easiest way to do this is to simply cast or convert the values to date data types:

select cast(StartTime as date) as CastToDate
      ,convert(date, StartTime, 103) as ConvertToDate   -- You may need to use 101 depending on your server setting for dd/mm/yyyy or mm/dd/yyyy

Script:

declare @StartDate date = '20170325'
        ,@EndDate date = '20170403';

-- Tally table to create dates to use in functions:
with n(n) as(select n from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n))
    ,d(d) as(select top(datediff(d,@StartDate,@EndDate)+1) dateadd(d,row_number() over (order by (select null))-1,@StartDate) from n n1,n n2,n n3,n n4,n n5,n n6)
select d
    ,datepart(week,d) as WeekNumber
    ,DATEADD(dd, -(DATEPART(dw, d)-1), d) as WeekCommencing
from d
order by d;

Output:

+------------+------------+----------------+
|     d      | WeekNumber | WeekCommencing |
+------------+------------+----------------+
| 2017-03-25 |         12 | 2017-03-19     |
| 2017-03-26 |         13 | 2017-03-26     |
| 2017-03-27 |         13 | 2017-03-26     |
| 2017-03-28 |         13 | 2017-03-26     |
| 2017-03-29 |         13 | 2017-03-26     |
| 2017-03-30 |         13 | 2017-03-26     |
| 2017-03-31 |         13 | 2017-03-26     |
| 2017-04-01 |         13 | 2017-03-26     |
| 2017-04-02 |         14 | 2017-04-02     |
| 2017-04-03 |         14 | 2017-04-02     |
+------------+------------+----------------+

niktrs · Answer 3 · 2017-06-13T10:44:07.127

0

Replace the field value in your SQL code with the expression below to remove time

DATEADD(dd, -(DATEPART(dw,[rhStartTime]) -1), DATEDIFF(dd, 0, [rhStartTime]) )

You can also achieve the same result by using the expression below in SSRS (change it to match your date field)

= DATEADD("d", - DATEPART(DateInterval.Weekday,Fields!rhStartTime.Value) +1,Fields!rhStartTime.Value)

edited Jun 13 '17 at 10:44

answered Jun 13 '17 at 10:37

niktrs

9,858
1
30
30

score 0 · Accepted Answer · answered Jun 13 '17 at 13:06

Thanks for the answers. I'm sure they probably would have worked if I were more competent. In the end I created a simple table on my server with year,weeknumber,commencedate as the column headings and manually created them in excel. Then I linked my results as a cte to that table where year = 2017 and cte.weeknumber = commencedate.weeknumber It seems to have worked.

Now in my SSRS report parameter I am using weeknumber as the value and commence date as the label. So I don't have to change any of the other configuration.

Turning week number into week commencing in SSRS/SQL Server

4 Answers4