I've searched the web for solutions but none of them quite fit what I want so here goes.
The Bradford Factor is used to measure employee absenteeism over a rolling twelve month period. I need to count the number of distinct absenteeism events in that period.
DECLARE @DateTest AS TABLE (Datex DATE, EmpNo INT, AbsenceNo INT, Sick VARCHAR (1)) DECLARE @DURATION INT SET @DURATION = 0 WHILE @DURATION > -400 BEGIN INSERT INTO @DateTest SELECT DATEADD (DAY, DATEDIFF(DAY, 0, GETDATE()) + @DURATION, 0) AS Datex, '20000' AS EmpNo, '0' AS AbsenceNo, 'N' AS Sick SET @DURATION = @DURATION - 1 END UPDATE @DateTest SET Sick = 'Y' WHERE Datex BETWEEN '2015/04/24' AND '2015/04/24' OR Datex BETWEEN '2015/04/27' AND '2015/04/27' OR Datex BETWEEN '2015/06/20' AND '2015/06/30' UPDATE @DateTest SET AbsenceNo = '44' WHERE Datex BETWEEN '2015/04/24' AND '2015/04/24' UPDATE @DateTest SET AbsenceNo = '49' WHERE Datex BETWEEN '2015/04/27' AND '2015/04/27' UPDATE @DateTest SET AbsenceNo = '74' WHERE Datex BETWEEN '2015/06/20' AND '2015/06/30' SELECT * FROM @DateTest
So, in this example for one employee, he/she has had a day off on 24/04/2015 (one occurrence), a day off on 27/04/2015 (one occurrence) and 11 days off between 20/06/2015 and 30/06/2015 (but in a single block, hence one occurrence).
What code would I use to return a count of occurrences (denoted by anything other than 0 in absence number) and the number of days sick (denoted by a Y in the Sick column)?
In this case, the answers would be 3 distinct occurrences, and 13 counts of Y if I were to run the query as of 23/04/2016?
If I were to run the query on 01/05/2016, the answers would be 1 distinct occurrence and 11 counts of Y.
Thank you for reading.