SQL Server - Get first date in a week, given the week number?

Question

I've got a query (for use in bug tracker.net) that calculates the number of bugs by week by status. But the query returns the week number, what I really want is the first date of the week

select datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date)))
       as [week], bg_status , st_name as [status], count(*) as [count] 
  from bugs inner join statuses on bg_status = st_id 
 group by datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))),
          bg_status, st_name
 order by [week], bg_status

The part that gets the week number is

datepart(wk, DateAdd(day, 0, DateDiff(day, 0, bg_reported_date))) as [week]

It returns this output:

week        bg_status   status                                        count
----------- ----------- --------------------------------------------- ------
22          1           new                                           1
22          5           closed                                        32

But it would be better to say the first date of each week, eg 01-01-2010, then 08-01-2010, etc

Question is not a duplicate of How do you get the "week start date" and "week end date" from week number in SQL Server? (answer says how to get week start from a date not from a week number)

Not a duplicate of Calculate date from week number (question asks for c#)

Not a duplicate of Get first date of week from provided date (question asks for javascript)

I did search but couldn't find this question answered for SQL Server (2010 if it matters)

Answer 1

If you think about it in the right way, the answer to SO 1267126 can be applied to your problem.

Each of the bug reported dates that you have in the group maps to the same week. By definition, therefore, each of those bug dates must also map to the same start of the week. So, you run the 'start of the week from given date' calculation on the bug report dates, as well as the week number calculation, and group by both (modestly ghastly) expressions, and end up with the answer you seek.

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date)
       AS [weekstart], bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1), bg_reported_date),
       bg_status, st_name
 ORDER BY [week], bg_status

---

Since bg_reported_date is a DATETIME (see the comment; it includes a time component), it is necessary to cast it to DATE before determining the week-start (but the week number expression doesn't need the cast, and the 'day of week' part of the week start expression doesn't need the cast either):

SELECT DATEPART(wk, DATEADD(day, 0, DATEDIFF(d, 0, bg_reported_date))) [week],
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE)) AS [weekstart],
       bg_status, st_name AS [status], COUNT(*) AS [count] 
  FROM bugs INNER JOIN statuses ON bg_status = st_id 
 GROUP BY DATEPART(wk, DATEADD(day, 0, DATEDIFF(day, 0, bg_reported_date))),
       DATEADD(dd, -(DATEPART(dw, bg_reported_date)-1),
               CAST(bg_reported_date AS DATE),
       bg_status, st_name
 ORDER BY [week], bg_status

NB: Untested code!

Answer 2

I realize this is a very old thread, but "Get first date in a week, given the week number" is exactly what I wanted to do and I do NOT have an actual date to work with, so the accepted answer would not work for me. I thought I'd post my solution for posterity. Note that I suspect different culture settings MAY break this, so test before using.

My answer is built starting from this one.

Let's assume you know a week number and a year and you want to get the start and end dates for that week of that year. Here's what I have:

--These 2 "declared" variables would be passed in somehow
declare @WeekNumber int = DATEPART(wk, GETDATE())
declare @ForYear int = YEAR(GETDATE())-1

--Since we don't have a raw date to work with, I figured I could just start with 
--Jan 1 of that year.  I'll store that date in a cte here, but if you are doing this
--in a stored proc or function, it would make much more sense to use another @variable
;with x as
(
    --this method works in SQL 2008:
    SELECT CONVERT(DateTime, ('1/1/' + CONVERT(varchar, @ForYear))) as Jan1ForSelectedYear
    --If you are using 2014 or higher, you can use this instead:
    --DATETIME2FROMPARTS(@ForYear, 1, 1, 0,0,0,0,0)
)
--Now that we have a date to work with, we'll just add the number of weeks to that date
--That will bring us to the right week number of the given year.
--Once we have THAT date, we can get the beginning and ending of that week
--Sorry to make you scroll, but I think this is easier to see what is going on this way
SELECT  CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear) - 6, x.Jan1ForSelectedYear))), 101) as FirstDayOfWeekXForSelectedYear,
        CONVERT(varchar(50), DateAdd(wk, (@WeekNumber - 1), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, x.Jan1ForSelectedYear)    , x.Jan1ForSelectedYear))), 101) as LastDayOfWeekXForSelectedYear
FROM x