credential = ##xp_cmdshell_proxy_account## to windows account
GRANT ALTER ON SCHEMA::dbo TO sqlPatientFeedbackHistory;
use master
grant exec opn sp_cmdshell to sqlPatientFeedbackHistory
grant exec on dbo.sp_OASetProperty to sqlPatientFeedbackHistory
grant exec on dbo.sp_OACreate to sqlPatientFeedbackHistory
grant exec on dbo.sp_OAMethod to sqlPatientFeedbackHistory
grant exec on dbo.sp_OADestroy to sqlPatientFeedbackHistory
Cursor between dates (format them nicely in output)
CREATE TABLE #tmpI (
WeekEnding DATETIME
,iCounts INT
)
DECLARE @workstation DATETIME
DECLARE @workstationEnd DATETIME
DECLARE WorkStationCursor CURSOR FAST_FORWARD
FOR
SELECT DISTINCT convert(NVARCHAR, DATEADD(ww, DATEDIFF(ww, 0, convert(NVARCHAR, dtClicked, 101)), 0), 101) AS dtClicked
,convert(NVARCHAR, DATEADD(ww, DATEDIFF(ww, 6, convert(NVARCHAR, dtClicked, 101)), 0), 101) AS dtClickedEnd
FROM [intranet].[dbo].[_ClickTracker]
WHERE dtClicked >= '03-01-2020'
AND RID = 16
ORDER BY dtClicked DESC
OPEN WorkStationCursor
FETCH NEXT
FROM WorkStationCursor
INTO @workstation
,@workstationEnd
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT cast(@workstationEnd AS NVARCHAR(20)) + ',' + cast(@workstation AS NVARCHAR(20))
INSERT INTO #tmpI
SELECT @workstation
,COUNT(dtClicked)
FROM [intranet].[dbo].[_ClickTracker]
WHERE dtClicked BETWEEN @workstationEnd
AND @workstation
--group by dtClicked
FETCH NEXT
FROM WorkStationCursor
INTO @workstation
,@workstationEnd
END
CLOSE WorkStationCursor
DEALLOCATE WorkStationCursor
SELECT convert(NVARCHAR(20), WeekEnding, 101) AS WeekEnding
,iCounts
FROM #tmpI
ORDER BY cast(WeekEnding AS DATE) DESC
DROP TABLE #tmpI
--DROP TABLE @tempTable