...
Er zijn in de MS Excel sheet alleen totaal uren geregistreerd over de gehele periode. Voor de demonstratie zijn deze uren opgedeeld in verschillende categorieën. Deze "opdeling" is in de tabel in SQL Server opgeslagen.
Er is een selectie van het personeel gebruikt omdat het REGAS tijdschrijven Excel sheet nog geen AFAS personeelsnummer bevat en gegeven op naam moeten worden gecombineerd. Dit is handmatig uitgevoerd.
De periode beslaat een aantal dagen van begin datum tot einddatum. Binnen deze periode vallen een aantal dagen waarin niet gewerkt wordt, zoals bv. weekeinden en officiële vakantiedagen. Hiervoor word de volgende functie gebruikt.
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
CREATE FUNCTION [dbo].[NetWorkDays](
@StartDate DATETIME
,@EndDate DATETIME
) RETURNS INT
AS
BEGIN
DECLARE
@result INT
,@StartDate2 DATETIME
,@EndDate2 DATETIME
,@HolidayCount INT
SET @StartDate2 = DATEADD(d,8-DATEPART(dw, @StartDate), @StartDate)
SET @EndDate2 = DATEADD(d,1-DATEPART(dw ,@EndDate), @EndDate)
SET @result = DATEDIFF(d, @StartDate2, @EndDate2) * 5 / 7
+ DATEDIFF(d, @StartDate, @StartDate2) - 1
+ DATEDIFF(d, @EndDate2, @EndDate)
- CASE WHEN DATEPART(dw,@StartDate) = 1 THEN 1 ELSE 0 END
- case when DATEPART(dw,@EndDate) = 7 THEN 1 ELSE 0 END
SET @HolidayCount = (SELECT COUNT(*) FROM Holiday WHERE [Date] BETWEEN @StartDate AND @EndDate)
RETURN @result - @HolidayCount
END |
De geregistreerde uren worden in een SQL view naar percentages berekend. Het percentage wordt op medewerker niveau berekend .
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
CREATE VIEW [dbo].[TijdschrijvenPersoneel]
AS
SELECT
T.[VolledigeNaam]
-- UREN
,[TotaalUren]
, dbo.NetWorkDays('2018-01-01','2018-04-04') AS NetWorkDays
, WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8 AS ExpectedHours
, [TotaalUren] - [OverlegUren] - [Training] - [Superuser] - [Vakgroephouder] - [Fondsenwerven] AS ClientUren
, [OverlegUren]
, [Training]
, [Superuser]
, [Vakgroephouder]
, [Fondsenwerven]
, ((WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) - [TotaalUren]) AS NietGeschreven
-- Percentages
, ([TotaalUren] - [OverlegUren] - [Training] - [Superuser] - [Vakgroephouder] - [Fondsenwerven])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS ClientPercentage
, ([OverlegUren])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS OverlegPercentage
, ([Training])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS TrainingPercentage
, ([Superuser])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8)* 100 AS SuperuserPercentage
, ([Vakgroephouder])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS VakgroephouderPercentage
, ([Fondsenwerven])/(WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS FondsenwervenPercentage
, ((WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) - [TotaalUren]) / (WerkzameUPW/40 * dbo.NetWorkDays('2018-01-01','2018-04-04') * 8) * 100 AS NietGeschrevenPercentage
FROM Personeel P
LEFT JOIN TijdSchrijven T on T.VolledigeNaam = P.VolledigeNaam
WHERE T.VolledigeNaam IS NOT NULL |