Dates from week number for a given number of weeks 

/*use intranet

select * from _ClickTracker where RID=13

*/

/*

select DATEDIFF(d, dtClicked, getdate())/7 as [weeks_ago], COUNT(ClientIP)

  from _ClickTracker

  WHERE  DATEDIFF(d, dtClicked, getdate()) <= 30*7  and RID=16 --NOT UNT='256' and   --<= 30*7

  group by DATEDIFF(d, dtClicked, getdate())/7

  ORDER BY weeks_ago

  */

 /* declare @wk int  = 1

select dateadd(week,@wk-1, DATEADD(wk, DATEDIFF(wk,-1,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)), 0)) */

 

  --https://stackoverflow.com/questions/13954803/sql-server-get-average-per-week-for-the-past-30-weeks

  /*

set datefirst 1;

select datepart(week, '2019-08-07');

SELECT DATEPART(wk, GETDATE())

select DATEPART(wk, GETDATE())-DATEDIFF(d, dtClicked, getdate())/7  as weeks_ago, COUNT(ClientIP) from _ClickTracker where RID=16

group by DATEDIFF(d, dtClicked, getdate())/7

ORDER BY weeks_ago DESC

--******************************

 */

select CONVERT(NVARCHAR(50),DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + '2019') + (DATEPART(wk, GETDATE())-DATEDIFF(d, dtClicked, getdate())/7-1), 6),110) AS StartOfWeek

,

   DATEPART(wk, GETDATE())-DATEDIFF(d, dtClicked, getdate())/as weeks_ago, COUNT(ClientIP) AS [Number of Clicks] from _ClickTracker where RID=10 AND DATEDIFF(d, dtClicked, getdate()) <= 30*7

group by DATEDIFF(d, dtClicked, getdate())/7

ORDER BY weeks_ago DESC

--******************************

/*

  convert(varchar(50), dateadd(dd, - datepart(dw, DATECOL) + 7, DATECOL), 101)

select  DATEPART(wk, GETDATE())

  */