/*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())/7 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())
*/