Bradford factor - counting consecutive incidences

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Bradford factor - counting consecutive incidences

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2006-10-24 : 11:30:18

The Bradford Factor is an indicator of somebody's propensity for sickness. sickness days x sickness incidences per annum. It's really quite an easy calculation once you have the figures to hand.

What I need to know is how to count the separate incidences of sickness that a person has. For example, if each row represents a day, how do I calculate the sickness incidences. e.g. how can I return 4 from this data:

Working
Working
Working
Sick
Working
Working
Working
Sick
Sick
Sick
Working
Working
Working
Working
Working
Sick
Sick
Working
Working
Sick
Working

Thanks
Mark
(stuck as usual :-) )

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-24 : 11:38:34

You can't. Without something to order the rows then it's not possible.
If you had a sequence on it

select count(distinct m) from
(select t1.id, m = count(*) from tbl t2, tbl t1 where t2.status = 'Working' and t2.seq < t1.seq and t1.status = 'Sick') a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mark1504
Posting Yak Master

103 Posts

Posted - 2006-10-24 : 12:08:32

Naturally I have something to order the rows - the date, but not sure what you mean by a sequence in this context.

If it can't be done, could you explain what the code you've posted is?

Cheers
Mark

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-24 : 12:14:07

You can use the date just as you would then seq #

If you want an example, read the hint link if my sig and post what iot asks for.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-24 : 12:37:44

>> If it can't be done, could you explain what the code you've posted is?

Read the post
"If you had a sequence on it"
That's the seq in the code. Just relpace that by the date and it should work.

>> Naturally I have something to order the rows
But naturally you decided not to post that.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mark1504
Posting Yak Master

103 Posts

Posted - 2006-10-24 : 12:47:42

Brett

Sorry, that didn't answer my question, but I realise it probably wasn't meant to.

Are you saying I shouldn't post a question without posting a table definition and sample data?

Here it is, if it will help...

CREATE TABLE Mark99
(
theday datetime,
Daytype varchar(1)
)
GO
INSERT INTO Mark99 VALUES ('01-Jan-2006','W')
INSERT INTO Mark99 VALUES ('02-Jan-2006','W')
INSERT INTO Mark99 VALUES ('03-Jan-2006','W')
INSERT INTO Mark99 VALUES ('04-Jan-2006','S')
INSERT INTO Mark99 VALUES ('07-Jan-2006','W')
INSERT INTO Mark99 VALUES ('08-Jan-2006','W')
INSERT INTO Mark99 VALUES ('09-Jan-2006','W')
INSERT INTO Mark99 VALUES ('11-Jan-2006','S')
INSERT INTO Mark99 VALUES ('12-Jan-2006','S')
INSERT INTO Mark99 VALUES ('13-Jan-2006','S')
INSERT INTO Mark99 VALUES ('14-Jan-2006','W')
INSERT INTO Mark99 VALUES ('15-Jan-2006','W')
INSERT INTO Mark99 VALUES ('16-Jan-2006','W')
INSERT INTO Mark99 VALUES ('19-Jan-2006','W')
INSERT INTO Mark99 VALUES ('20-Jan-2006','W')
INSERT INTO Mark99 VALUES ('21-Jan-2006','S')
INSERT INTO Mark99 VALUES ('22-Jan-2006','S')
INSERT INTO Mark99 VALUES ('23-Jan-2006','W')
INSERT INTO Mark99 VALUES ('24-Jan-2006','W')
INSERT INTO Mark99 VALUES ('25-Jan-2006','S')
INSERT INTO Mark99 VALUES ('26-Jan-2006','S')
INSERT INTO Mark99 VALUES ('27-Jan-2006','W')
INSERT INTO Mark99 VALUES ('28-Jan-2006','W')
INSERT INTO Mark99 VALUES ('29-Jan-2006','W')
INSERT INTO Mark99 VALUES ('30-Jan-2006','W')
GO

Thanks
Mark

mark1504
Posting Yak Master

103 Posts

Posted - 2006-10-24 : 12:54:28

Thanks nr

I thought I'd asked in simple and a civil manner. Don't know why I seem to have annoyed people with this question.

Regards
Mark

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-24 : 13:01:52

It would have helped to include the table structure at the outset, or at least mention that you had a date column. It's a crucial fact about your data that determines if there's a solution or not. Using only the information you originally provided, Nigel's response was accurate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 13:14:53

And if you still can't figure it out (since there is a group by problem with nr suggestion), here it is in plaintext.

select count(distinct m) from
(select m = count(*) from mark99 t2 cross join mark99 t1 where t2.daytype = 'W' and t2.theday < t1.theday and t1.daytype = 'S' group by t1.theday) a

Peter Larsson
Helsingborg, Sweden

mark1504
Posting Yak Master

103 Posts

Posted - 2006-10-24 : 13:21:30

Thanks all, I'll try to be more explicit next time.

And thanks Peter, your suggestion is brilliant, my first experience with a cross join.

Regards
Mark

CASE CLOSED

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 13:22:43

Don't thank me. Thank nr!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources