How to extract continuous data.

  • 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

  • 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

  • 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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • 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"

  • Oh Man great, thanks a lot it works like a miracle.

    thank you again

  • 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