--absence type table
create table #absencetype
(absencetypeid int,
absencetype varchar(20)
)

insert #absencetype
select 1, 'None' union all
select 2, 'Holiday' union all
select 3, 'Sickness'

--table that tells us (among other things) what date users were absent on
create table #flexrecord (userid int,flexdate datetime,absencetypeid int)

insert #flexrecord
select 1, '10/10/2011', 1 union all
select 1, '11/10/2011', 3 union all
select 1, '12/10/2011', 1 union all
select 1, '13/10/2011', 1 union all
select 1, '14/10/2011', 3 union all
select 1, '17/10/2011', 3 union all
select 1, '18/10/2011', 1 union all
select 1, '19/10/2011', 1 union all
select 1, '20/10/2011', 1 union all
select 1, '21/10/2011', 1 union all
select 1, '24/10/2011', 1 union all
select 1, '25/10/2011', 3 union all
select 1, '26/10/2011', 3 union all
select 1, '27/10/2011', 3 union all
select 1, '28/10/2011', 1

--working patterns table
create table #FlexHours (userid int, DayOfWeek int, FlexHours datetime)
insert #FlexHours
select 1, 1, '01/01/1900 08:00:00' union all
select 1, 2, '01/01/1900 08:00:00' union all
select 1, 3, '01/01/1900 00:00:00' union all
select 1, 4, '01/01/1900 08:00:00' union all
select 1, 5, '01/01/1900 08:00:00' 

select
userid,
count(userid)
from #flexrecord 
where absencetypeid = 3
group by userid

select
fr.userid,
count(fr.userid)
from #flexrecord fr 
join 
	(
	select * from 
	#flexhours 
	where flexhours <> '01/01/1900 00:00:00' -- indicates contracted day off
	) fh
on fr.userid = fh.userid and datepart(dw, fr.flexdate) = fh.dayofweek

where fr.absencetypeid = 3
group by fr.userid

WITH WorkGrps
AS
(
	SELECT R.userid, R.absencetypeid
		,ROW_NUMBER() OVER (PARTITION BY R.userid ORDER BY R.flexdate)
			- ROW_NUMBER() OVER (PARTITION BY R.userid ORDER BY R.absencetypeid, R.flexdate) AS grp
	FROM #flexrecord R
		JOIN #FlexHours H
			ON r.userid = h.userid
				AND DATEPART(dw, r.flexdate) = H.[DayOfWeek]
				AND H.FlexHours > '19000101'
	WHERE R.flexdate >= '20110101'
		AND R.flexdate < '20120101'
)
, Absence
AS
(
	SELECT userid
		,COUNT(DISTINCT grp) AS Periods
		,COUNT(userid) AS DaysOff
	FROM WorkGrps
	WHERE absencetypeid = 3
	GROUP BY  userid
)
-- select * from Absence
SELECT userid, Periods, DaysOff
	,Periods * Periods * DaysOff As BradfordFactor
FROM Absence

SELECT fr.userid, fr.flexdate, fh.DayOfWeek
  INTO #T1
  FROM #flexrecord AS fr 
       LEFT OUTER JOIN
       (SELECT *
          FROM #flexhours
         WHERE flexhours <> '01/01/1900 00:00:00') AS fh
       ON fr.userid = fh.userid
          AND DATEPART(dw, fr.flexdate) = fh.dayofweek
 WHERE fr.absencetypeid = 3

INSERT INTO #T1(userid, flexdate, DayOfWeek)
   SELECT userid, DATEADD(DAY, 1, flexdate), 6
     FROM #T1
    WHERE DayOfWeek = 5;

INSERT INTO #T1(userid, flexdate, DayOfWeek)
   SELECT userid, DATEADD(DAY, 2, flexdate), 7
     FROM #T1
    WHERE DayOfWeek = 5;

CREATE INDEX __T1__idx__userid__flexdate ON #T1(userid, flexdate);

SELECT T.userid, COUNT(T.userid) AS periods_count 
  FROM (SELECT T.userid,
               MIN(T.flexdate) AS start_of_period,
               MAX(T.flexdate) AS end_of_period
          FROM (SELECT T1.userid, T1.flexdate,
                       (SELECT MIN(T2.flexdate)
                          FROM #T1 AS T2
                         WHERE T2.userid = T1.userid
                           AND T2.flexdate >= T1.flexdate
                           AND NOT EXISTS(SELECT *
                                            FROM #T1 AS T3
                                           WHERE T3.userid = T2.userid
                                             AND T3.flexdate = DATEADD(DAY, 1, T2.flexdate))) AS grouping_factor
                  FROM #T1 AS T1) AS T
        GROUP BY userid, grouping_factor) AS T
 GROUP BY T.userid;

quote:

---

Originally posted by yonabout

Do you have to include the weekend days so the DATEADD bit of the grouping_factor field works properly?

---

quote:

---

Originally posted by yonabout

Also are there any articles on that technique that you know of - I'd like to get my head around it a bit more.

---