Count consecutive data 1

[yoshi88]

Hi, I'm trying to count consecutive data in this table:

EmplNo Day Code
1 1 Sick
1 2 Sick
1 5 Sick
1 6 At work
1 7 Sick
2 1 Sick
2 2 At work
2 3 Sick
2 6 At work
2 7 Sick

I want to know how many consecutive sick incident we have per employee. The result should be like that:

EmpNo Incidence
1 2
2 3

Employee 1 was sick 3 days in a row even if there was a weekend on day 4 and 5 (I dont have any data in my table for the day off)

How can I create a query to calculate the number of incidence per employe? I have already tried to find a solution in the previous post but I haven't found any answer.


Frank

 

[CautionMP]

Basically you want a query that assigns the value 1 to the first sick day in a (consecutive) run and a 0 for the follwing days so you can sum them to find the number of incidents?

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[yoshi88]

Exactly what I'm looking for.

Thanks


Frank

 

[PHV]

A some complicated way, but all in pure SQL.
Create a query named, say, qryDistinctSickDays, to get the non consecutive sick days of each employee:

SELECT DISTINCT X.EmplNo, X.Day
FROM (SELECT A.EmplNo, A.Day, A.Code, COUNT(*) AS Rank
FROM tblEmpDays AS A INNER JOIN tblEmpDays AS B ON A.EmplNo = B.EmplNo AND B.Day<=A.Day
GROUP BY A.EmplNo, A.Day, A.Code
) AS X INNER JOIN (SELECT A.EmplNo, A.Day, A.Code, COUNT(*) AS Rank
FROM tblEmpDays AS A INNER JOIN tblEmpDays AS B ON A.EmplNo = B.EmplNo AND B.Day<=A.Day
GROUP BY A.EmplNo, A.Day, A.Code
) AS Y ON X.EmplNo = Y.EmplNo
WHERE ABS(Y.Rank - X.Rank) = 1
AND X.Code = 'Sick' AND Y.Code <> 'Sick';

Now, your counting query:

SELECT EmplNo, COUNT(*) AS Incidence
FROM qryDistinctSickDays
GROUP BY EmplNo;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Yeah, I came up with about the same thing with a couple of steps.

Assumes table [tt]tblCountConsecutive[/tt] has the same structure listed above (EmplNo, iDay, Code).

Created a query [tt]qryCountConsecutiveSick[/tt] with all the same fields with a 'q' in front (qEmplNo, qDay, qCode).

Here is the SQL for the query that returns 1 or 0:[tt]
[navy]SELECT[/navy] qryCountConsecutiveSick.qEmplNo, qryCountConsecutiveSick.qDay, IIf(([blue]SELECT DISTINCT TOP 1[/blue] iDay [blue]FROM[/blue] tblCountConsecutive [blue]WHERE[/blue] (((tblCountConsecutive.EmplNo)=qEmplNo) [blue]AND[/blue] ((tblCountConsecutive.iDay)>qDay) [blue]AND[/blue] ((tblCountConsecutive.Code)="Sick"))-1=1,0,1) [navy]AS[/navy] qIncidence
[navy]FROM[/navy] qryCountConsecutiveSick;[/tt]

Then the last step would be to do a Group By query on this and count [tt]qIncidence[/tt]

With all that said, if your goal is to show this on a form or report it might be easier to construct a function that calculates this when the form/report is loaded.

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[PHV]

CMP, are you sure the TOP predicate will always return expected result without an ORDER BY clause ?
Furthermore I'm not sure your IIf take in account the gaps for the non worked days.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

Yeah, I pretty much missed the target.

Happens some times.

CMP

Funny thing about being unemployed, weekends don't mean quite so much, just means you get to hang out with your working friends. Primus

 

[yoshi88]

Thanks for your answers. But I still have a problem with the results. I have different code for someone who is sick depending on the reason. PHV I used your query and I change it a bit:

SELECT DISTINCT X.EmplNo, X.Day
FROM qry2, [SELECT A.EmplNo, A.Day, A.Code, COUNT(*) AS Rank
FROM qrycode AS A INNER JOIN qrycode AS B ON A.EmplNo = B.EmplNo AND B.Day<=A.Day
GROUP BY A.EmplNo, A.Day, A.Code
]. AS X INNER JOIN [SELECT A.EmplNo, A.Day, A.Code, COUNT(*) AS Rank
FROM qrycode AS A INNER JOIN qrycode AS B ON A.EmplNo = B.EmplNo AND B.Day<=A.Day
GROUP BY A.EmplNo, A.Day, A.Code
]. AS Y ON X.EmplNo = Y.EmplNo
WHERE (((Abs([Y].[Rank]-[X].[Rank]))=1) AND ((X.Code)=[qry2].

) AND ((Y.Code)<>[qry2].[code]));

The qrycode is a query use to convert the date on a regular format.  The date are in 3 fields.  The qry2 is a list of all the possible codes.

Here is the data from the qrycode
emplno	day	code
240	31-01-06	sick
240	01-02-06	sick
240	02-02-06	
240	03-02-06	othersick
240	04-02-06	othersick
240	07-02-06	othersick
240	08-02-06	
240	09-02-06	sick
240	10-02-06	othersick
240	11-02-06	noreason
240	14-02-06	othersick
240	15-02-06	late
240	16-02-06	late
240	17-02-06	sick
240	18-02-06	sick

and here is the data from qry2
code
             **empty line**
late
noreason
othersick
sick

and here is the result from the sql code you send me
EmplNo	Day
240	09-02-2006
240	10-02-2006
240	11-02-2006
240	14-02-2006
240	15-02-2006
240	16-02-2006
240	17-02-2006

The 31-01-2006 to 08-02-2006 data are missing.


Thanks again for your help


Frank

 

[PHV]

So, why asking with wrong rules ?
What is the data type of qrycode.Day ?
In fact, why not posting the SQL code of qrycode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[yoshi88]

At first I was looking for only one type of code and then we had to change it for all the codes to check the absenteeism. Now I have more that 100 different codes to check. But I assume the exemple I gave you in the last reply is relevant to the new situation.

The TblEmployeeData has those fields:
emplno
year
month
day1
code

The qrycode sql is:
SELECT DISTINCTROW TblEmployeeData.emplno, DateSerial([Year],[Month],[DAY1]) AS [day], TblEmployeeData.code
FROM TblEmployeeData
ORDER BY TblEmployeeData.emplno, DateSerial([Year],[Month],[DAY1]);



Thanks

 

[PHV]

But I assume the exemple I gave you in the last reply is relevant to the new situation
Any chance you could post expected result with this exemple ?
Which codes should be counted as incidence (or ignored) ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886