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.
Select
Date column
, RR
From
Table
Where
Date column >= datediff(dd,-28, date column)
Union all
Select
Date column
, Avg( RR )
From
Table
Where
Date column >= datediff(dd,-28, date column)
I've tried a few variations of that but it isn't pulling the right hours for some reason. Here's how I have the query at the moment:
SELECT
[Date]
,[Skill]
,[Weekday]
,avg(Absence) as Absence
FROM [dbo].[Archive]
where datediff(day,[Job Date],[Date]) = -1
and Date >= cast (getdate()-28 as date)
group by skill, Weekday, Date
For the 7th of April using the same data source as above, the Absence hours returned are 212 rather than 235. Not sure what the cause of the difference is.
some times this is the issue
when you put aggregate functions directly
; with cte as
(
SELECT
[Date]
, [Skill]
, [Weekday]
, Absence
FROM
[dbo].[Archive]
WHERE
datediff(day,[Job Date],[Date]) = -1
AND
Date >= cast (getdate()-28 as date)
)
select [Date], [Skill] , [Weekday], AVG(Absence) as Absence
from cte
group by skill, Weekday, Date
