Modify SQL for calculating Bradford Factor to incorperate a rolling year

[Mattbro]

Hello all,
I was looking at a solution for a Bradford Factor database (calculate staff sickness based on days sick *days sick*no of sickness events). I saw this on another forum (http://www.access-programmers.co.uk/...d.php?t=229470);
tblStaff with staffID, surname, forname etc.
TblAbsence with AbsenceID, StaffID, AbsenceType (e.g. sick, compassionate), AbsenceStartDate and AbsenceLength.
The SQL suggested was

SELECT tblStaff.StaffID, Sum([AbsenceLength])*Sum([AbsenceLength])*Count([AbsenceStartDate]) AS BradFordFactor, tblStaff.Surname, tblStaff.Forename, tblAbsence.AbsenceStartDate
FROM tblStaff INNER JOIN tblAbsence ON tblStaff.StaffID = tblAbsence.StaffID
GROUP BY tblStaff.StaffID, tblStaff.Surname, tblStaff.Forename, tblAbsence.AbsenceType, tblAbsence.AbsenceStartDate
HAVING (((tblAbsence.AbsenceType)="sick") :

This is great, but the Bradford Factor is calculated over a rolling year. I am trying to modify it on the AbsenceStartDate having to be less than one year from today. I have been playing with Date()>DateAdd("d",-365,Date() but am so far unsuccessful. Does anyone have any ideas how to incorporate it into the SQL?
Many thanks,
Mattbro

[June7]

Criteria that compares the current date to a value calculated from the current date doesn't make sense. The criteria should be against a date field in the table.

You only want to consider records that are between now and now-365?

WHERE AbsenceStartDate BETWEEN Date()-365 AND Date()

Or assuming there can't be records dated beyond the current date:

WHERE AbsenceStartDate > Date()-365

[Mattbro]

Ah-right you are, June7

SELECT tblStaff.StaffID, Sum(tblAbsence.AbsenceLength) AS SumOfAbsenceLength, Count(tblAbsence.AbsenceStartDate) AS CountOfAbsenceStartDate, Sum([AbsenceLength])*Sum([AbsenceLength])*Count([AbsenceStartDate]) AS BradFordFactor, tblStaff.Surname, tblStaff.Forename
FROM tblStaff INNER JOIN tblAbsence ON tblStaff.StaffID = tblAbsence.StaffID
WHERE AbsenceStartDate BETWEEN Date()-365 AND Date()
GROUP BY tblStaff.StaffID, tblAbsence.AbsenceType, tblStaff.Surname, tblStaff.Forename
HAVING (((tblAbsence.AbsenceType)="sick"));

Works a treat! Many thanks,
Mattbro

[Mattbro]

Hello all,
I don't know if this will garner any attention, reopening my own thread, but it has just been pointed out to me that my formula is slightly incorrect. The Bradford factor is based on events*events*days, which is incorporated into the SQL (changed slightly from earlier, as I was using events*days*days!)

Sum([AbsenceLength])*Count([AbsenceStartDate])*Count([AbsenceStartDate]) AS BradFordFactor

and it works on a rolling year, which is also incorporated using

BETWEEN Date()-365 AND Date()

however, the formula doesn't allow for an individual who has had long term sick leave where the first day of absence is over a year ago but the last day of absence is less than a year ago. I need a count of days between:
Date()-365 and the AbsenceEndDate IF the AbsenceStartDate >365 days ago AND the AbsenceEndDate is < 365 days ago.
If that criteria exists, I then need to include it somehow by altering

Count(tblAbsence.AbsenceStartDate) AS CountOfAbsenceStartDate.

I could do it by changing it to

Count(tblAbsence.AbsenceEndDate) ASCountofAbsenceEndDate

If anyone can see how to code this, I would be very very interested as it is far beyond my capabilities...perhaps a second query?? Please help!
Many thanks
Mattbro

[orange]

see this for basic calculation
http://en.wikipedia.org/wiki/Bradford_Factor

[Dal Jeanis]

I'd start by creating a subquery that creates/calculates the relevant absences, and then do the calc on that.

This will give you the EFFECTIVE start and end dates and EFFECTIVE duration of each event in the period:

SELECT
   TA.StaffID,
   TA.AbsenceType,
   IIF(TA.AbsenceStartDate >= Date() - 365,
       TA.AbsenceStartDate, 
       Date() - 365
      ) As AbsenceStartDate,
   TA.AbsenceStartDate + TA.AbsenceLength As AbsenceEndDate,
   IIF(TA.AbsenceStartDate >= Date() - 365,
       TA.AbsenceLength,
       Int(TA.AbsenceStartDate + TA.AbsenceLength - Date() + 365)
      ) AS AbsenceLength
FROM   
   tblAbsence AS TA
WHERE  TA.AbsenceType="sick"
  AND  TA.AbsenceStartDate + TA.AbsenceLength >= Date() - 365;

The Count of those records is the count of the events. The sum of the absenceLength is the total duration within the last year.

Then Join to the tblStaff table to get the name. You don't need to group by the names, just accept the first one or max or min, since they're all the same for each staffID.

Not sure why you wanted the AbsenceStartdate, or which of the possible dates that you really wanted. I'm going to give you the earliest start and latest end dates.

So, your Bradford calc should look somewhat like this:

SELECT 
   TS.StaffID, 
   First(TS.Surname) As Surname, 
   First(TS.Forename) As Forename,
   Count(TA2.AbsenceLength])*Count(TA2.AbsenceLength])*Sum(TA2.AbsenceLength) AS BradFordFactor, 
   Min(TA2.AbsenceStartDate) AS FirstAbsence,
   Max(TA2.AbsenceEndDate) AS LastAbsence
FROM 
   tblStaff AS TS 
   INNER JOIN 
     (SELECT
         TA.StaffID,
         TA.AbsenceType,
         IIF(TA.AbsenceStartDate >= Date() - 365,
             TA.AbsenceStartDate, 
             Date() - 365
            ) As AbsenceStartDate,
         TA.AbsenceStartDate + TA.AbsenceLength As AbsenceEndDate,
         IIF(TA.AbsenceStartDate >= Date() - 365,
             TA.AbsenceLength,
             Int(TA.AbsenceStartDate + TA.AbsenceLength - Date() + 365)
            ) AS AbsenceLength
      FROM   tblAbsence AS TA
      WHERE  TA.AbsenceType="sick"
        AND  TA.AbsenceStartDate + TA.AbsenceLength >= Date() - 365
      ) AS TA2
   ON TS.StaffID = TA2.StaffID
GROUP BY 
   TS.StaffID;

[Mattbro]

This is just fantastic! Many thanks, Dal-I am going to spend some time looking at the SQL here, to see if I can pick up some ideas. Thanks once again.
Mattbro.

[Dal Jeanis]

No prob. A lot of times, when I have a complex one like this, I think in levels. If I had a table that had *this* information, then I could build my query pretty easily. How can I flatten *these* tables to make a query that mimics the talbe I want?

In this case, the most complicated issue was how to turn the real absence table into an "effective" absence table, then the rest fell into place easily enough. You might want to verify whether that 365 should be 364 or 363 instead. The article said it's normally a 52-week (which is 364-day) rolling average, and I'm not sure whether you should include both today and the first day of that period.