Try something like this:
DECLARE @tmpDate AS smalldatetime
DECLARE @Yesterday AS smalldatetime
DECLARE @OneWeek AS smalldatetime
DECLARE @Fortnight AS smalldatetime
DECLARE @Month AS smalldatetime
DECLARE @Offset AS INT
TRUNCATE TABLE dbo.COSStudiesAV
SET ROWCOUNT 0
SELECT distinct(DATEADD(dd, DATEDIFF(dd, 0, DateAndTime), 0)) as 'DateAndTime'
INTO #TempTable
FROM dbo.COSStudies
ORDER BY DateAndTime DESC
ALTER TABLE #TempTable ADD ColumnID INT IDENTITY(1,1)
SET @tmpDate = (SELECT TOP 1 DateAndTime FROM #TempTable ORDER BY DateAndTime DESC)
IF YEAR(@tmpDate) = YEAR(GETDATE()) AND MONTH(@tmpDate) = MONTH(GETDATE()) AND DAY(@tmpDate) = DAY(GETDATE())
SET @Offset = 1
ELSE
SET @Offset = 0
SET @Yesterday = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +1)
SET @OneWeek = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +5)
SET @Fortnight = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +10)
SET @Month = (SELECT DateAndTime FROM #TempTable WHERE ColumnID = @Offset +20)
print @Yesterday
print @OneWeek
print @Fortnight
print @Month
WITH CTE AS
(
SELECT C.code,
T1.Hour10to11AvOne, T1.Hour11to12AvOne, T1.Hour12to13AvOne,
T1.Hour13to14AvOne, T1.Hour14to15AvOne, T1.Hour15to16AvOne,
T2.Hour10to11AvWeek, T2.Hour11to12AvWeek, T2.Hour12to13AvWeek,
T2.Hour13to14AvWeek, T2.Hour14to15AvWeek, T2.Hour15to16AvWeek,
T3.Hour10to11AvFortnight, T3.Hour11to12AvFortnight, T3.Hour12to13AvFortnight,
T3.Hour13to14AvFortnight, T3.Hour14to15AvFortnight, T3.Hour15to16AvFortnight,
T4.Hour10to11AvMonth, T4.Hour11to12AvMonth, T4.Hour12to13AvMonth,
T4.Hour13to14AvMonth, T4.Hour14to15AvMonth, T4.Hour15to16AvMonth
FROM (SELECT DISTINCT(COSStudies.Code) AS code
FROM dbo.COSStudies) AS C
OUTER APPLY
(SELECT AVG(CS.Hour10to11) AS Hour10to11AvOne,
AVG(CS.Hour11to12) AS Hour11to12AvOne,
AVG(CS.Hour12to13) AS Hour12to13AvOne,
AVG(CS.Hour13to14) AS Hour13to14AvOne,
AVG(CS.Hour14to15) AS Hour14to15AvOne,
AVG(CS.Hour15to16) AS Hour15to16AvOne
FROM dbo.COSStudies AS CS
WHERE CS.DateAndTime <= @Yesterday
AND CS.DateAndTime >= @Yesterday
AND CS.Code = C.code) AS T1
OUTER APPLY
(SELECT AVG(CS.Hour10to11) AS Hour10to11AvWeek,
AVG(CS.Hour11to12) AS Hour11to12AvWeek,
AVG(CS.Hour12to13) AS Hour12to13AvWeek,
AVG(CS.Hour13to14) AS Hour13to14AvWeek,
AVG(CS.Hour14to15) AS Hour14to15AvWeek,
AVG(CS.Hour15to16) AS Hour15to16AvWeek
FROM dbo.COSStudies AS CS
WHERE CS.DateAndTime <= @Yesterday
AND CS.DateAndTime >= @OneWeek
AND CS.Code = C.code) AS T2
OUTER APPLY
(SELECT AVG(CS.Hour10to11) AS Hour10to11AvFortnight,
AVG(CS.Hour11to12) AS Hour11to12AvFortnight,
AVG(CS.Hour12to13) AS Hour12to13AvFortnight,
AVG(CS.Hour13to14) AS Hour13to14AvFortnight,
AVG(CS.Hour14to15) AS Hour14to15AvFortnight,
AVG(CS.Hour15to16) AS Hour15to16AvFortnight
FROM dbo.COSStudies AS CS
WHERE CS.DateAndTime <= @Yesterday
AND CS.DateAndTime >= @Fortnight
AND CS.Code = C.code) AS T3
OUTER APPLY
(SELECT AVG(CS.Hour10to11) AS Hour10to11AvMonth,
AVG(CS.Hour11to12) AS Hour11to12AvMonth,
AVG(CS.Hour12to13) AS Hour12to13AvMonth,
AVG(CS.Hour13to14) AS Hour13to14AvMonth,
AVG(CS.Hour14to15) AS Hour14to15AvMonth,
AVG(CS.Hour15to16) AS Hour15to16AvMonth
FROM dbo.COSStudies AS CS
WHERE CS.DateAndTime <= @Yesterday
AND CS.DateAndTime >= @Month
AND CS.Code = C.code) AS T4
)
INSERT INTO dbo.COSStudiesAv(Code, StartDateOne, EndDateWeek, EndDateFortnight, EndDateMonth,
Hour10to11AvOne, Hour11to12AvOne, Hour12to13AvOne,
Hour13to14AvOne, Hour14to15AvOne, Hour15to16AvOne,
Hour10to11AvWeek, Hour11to12AvWeek, Hour12to13AvWeek,
Hour13to14AvWeek, Hour14to15AvWeek, Hour15to16AvWeek,
Hour10to11AvFortnight, Hour11to12AvFortnight, Hour12to13AvFortnight,
Hour13to14AvFortnight, Hour14to15AvFortnight, Hour15to16AvFortnight,
Hour10to11AvMonth, Hour11to12AvMonth, Hour12to13AvMonth,
Hour13to14AvMonth, Hour14to15AvMonth, Hour15to16AvMonth)
SELECT code, @Yesterday, @OneWeek, @Fortnight, @Month,
Hour10to11AvOne, Hour11to12AvOne, Hour12to13AvOne,
Hour13to14AvOne, Hour14to15AvOne, Hour15to16AvOne,
Hour10to11AvWeek, Hour11to12AvWeek, Hour12to13AvWeek,
Hour13to14AvWeek, Hour14to15AvWeek, Hour15to16AvWeek,
Hour10to11AvFortnight, Hour11to12AvFortnight, Hour12to13AvFortnight,
Hour13to14AvFortnight, Hour14to15AvFortnight, Hour15to16AvFortnight,
Hour10to11AvMonth, Hour11to12AvMonth, Hour12to13AvMonth,
Hour13to14AvMonth, Hour14to15AvMonth, Hour15to16AvMonth
FROM CTE
DROP TABLE #TempTable
Even though I should alter another parts of your script, or ask for more information to help me grasp the intention of them, I have concentrated on eliminating the cursor only, and I hope that this would work (it is hard to write queries when tables, and sample data is missing).
So try it, and let us know if it is working, and if it is fast enough.
