Bradford Factor calculation

Transact-SQL
1

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.

2

Maybe this:

declare @dt date=cast('2016-05-01' as date);
select empno
      ,count(distinct absenceno) as times_sick
      ,count(absenceno) as days_sick
  from @datetest
 where sick='Y'
   and datex>dateadd(year,-1,@dt)
   and Datex<=@dt
 group by empno
;
1 Like
3

Thank you, works like a charm.

Embarrassingly easy when you know how :frowning: