Hi, I'm looking to create a table in SQL Server looking at the archived data and supplying what the relevant 4 week run rate was at any given time. For example, today is Friday the 7th of April so I'd like the table to look at the last 4 Fridays and average what the actual absence hours were:
The archive runs once a day for 7 days in the past and 90 days forward, so there will be multiple entries for the 7th of April. To avoid any duplication I'd only need it to look at the job one day out - the absence for the 31st of March from the job ran on the 1st of April.
I have the solution in excel but I'm struggling to translate that on SQL. Any help would be appreciated.
some times this is the issue
when you put aggregate functions directly
; with cte as
datediff(day,[Job Date],[Date]) = -1
Date >= cast (getdate()-28 as date)
select [Date], [Skill] , [Weekday], AVG(Absence) as Absence
group by skill, Weekday, Date