Convert iso_week to calendar date in SQL

I’ve been searching though the archives without finding what I am looking for- I’d be happy for some guidance.

I have a data set where I want to report aggregated number of appointments by provider (STAFFID) and work week, the latter defined by the week’s Monday date. I’ve played with datepart(iso_week, appointment_date) as week_of_yr which gets me part of the way there- I can group by week to get the right numbers. However, I can’t figure out if there’s a simple way to display the date of the week’s Monday given the iso_week integer (and year).

I found ISO8601 Convert Week Date to Calendar Date helpful, though I do not know whether (or how) I can automate that process for many values at once.

Here’s the tidbit of code I have. Ideally I could add another expression to the select statement which would display the desired date.

     select STAFFID
     , count(*) as appointment_ct
     , datepart(iso_week, appointment_date) as iso_wk --this returns the week # of the year as an int
     from [dbo].[view_APPT_DATA]
     where program_code in ('99999') 
     and appointment_date >= '1/1/2016' and appointment_date <='3/31/2016' 
     group by iso_wk, STAFFID

Answer

I would find the first Monday of that year and then use DATEADD to add the number of weeks to that day

 select STAFFID
 , count(*) as appointment_ct
 , datepart(iso_week, appointment_date) as iso_wk --this returns the week # of the year as an int
 , dateadd(week, datepart(week, DATEADD(DAY, (@@DATEFIRST - DATEPART(WEEKDAY, dateadd(year, datepart(year, appointment_date) - 1900, 0)) + (8 - @@DATEFIRST) * 2) % 7, dateadd(year, datepart(year, appointment_date) - 1900, 0)))  as monday_wk 
 from [dbo].[view_APPT_DATA]
 where program_code in ('99999') 
 and appointment_date >= '1/1/2016' and appointment_date <='3/31/2016' 
 group by iso_wk, STAFFID, monday_wk