August 10, 2008 at 11:11 pm
My table tblattendance have below data;
DateEmployee CodeNameStatus
8/4/2008 0:003Aabsent
8/1/2008 0:001Babsent
8/2/2008 0:001Babsent
8/3/2008 0:001Babsent
8/10/2008 0:001Babsent
8/4/2008 0:009Cabsent
8/4/2008 0:005Gabsent
8/8/2008 0:005Gabsent
8/9/2008 0:005Gabsent
8/10/2008 0:005Gabsent
I want to make a query or store procedure that only show records who is absent continuously 3 days i.e. (day 1,2,3) not randomly i.e. (day 4,8,9,10).
Any help will be highly appreciated.
thanks / Eashrak
August 11, 2008 at 1:50 am
OK, since you're only looking for 3 consecutive days. it was hard for me to tell whether it is best to use recursive or regular CTE. I used regular to keep it simple , but if you need more than 3 I suggest you leverage it to recursive.
first, let's set up the table:
CREATE TABLE [dbo].[ABSENCE_DEMO](
[ID] [int] IDENTITY(1,1) NOT NULL,
[student] [varchar](10) COLLATE SQL_Latin1_General_CP1255_CI_AS NOT NULL,
[dt] [datetime] NOT NULL,
[IsAbsent] [bit] NOT NULL,
CONSTRAINT [PK__Attendance__03317E3D] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
now, let's fill it with some data:
put your own data here
and then, the query:
WITH
ABSENCE_CTE AS (SELECT STUDENT, DT FROM ABSENCE_DEMO)
SELECT
D1.STUDENT, D1.DT D1, D2.DT D2, D3.DT D3
FROM
ABSENCE_CTE D1
INNER JOIN ABSENCE_CTE D2 ON
D1.STUDENT = D2.STUDENT
AND
D1.DT+1 = D2.DT
INNER JOIN ABSENCE_CTE D3 ON
D2.STUDENT = D3.STUDENT
AND
D2.DT+1 = D3.DT
I put only absence records, but you could put both attendance and absence records by adding a where clause to the CTE
Hope that's what you needed...
Tal
August 11, 2008 at 2:02 am
You could use something like
WITH DG
AS ( SELECT DATEDIFF(day,
row_number() OVER ( PARTITION BY [Employee Code] ORDER BY Date ),
Date) AS DayGroup
, [Date]
, [Employee Code]
, [Name]
, [Status]
FROM tblattendance
)
SELECT [Employee Code]
, MIN(Date) StartDayOfAbsence
, COUNT(*) NumberOfDayAbsent
FROM DG
GROUP BY [Employee Code]
, DayGroup
HAVING COUNT(*) >= 2
This will give you:
Employee Code StartDayOfAbsence NumberOfDayAbsent
------------- ----------------------- -----------------
1 2008-08-01 00:00:00.000 3
5 2008-08-08 00:00:00.000 3
(2 row(s) affected)
Explanation, with the datediff & rownumber contiguous days will get a number assigned to them, and then it is just finding groups of these where there are at leas three entries involved.
The above is based on the assumption that every day counts. I.e. Saturday, Sunday, etc, so we are not talking about weekends, holidays, etc.
Regards,
Andras
August 11, 2008 at 3:41 am
Also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108531
N 56°04'39.16"
E 12°55'05.25"
August 11, 2008 at 10:25 pm
Oh Man great, thanks a lot it works like a miracle.
thank you again
February 18, 2011 at 5:48 am
Awesome Dude... its help me a lottttt....:-)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply