Here's one possible answer...

Assuming the "ACCESS_SOURCE" is a table, we need to first combine the Event_D and Event_T columns into a single, sortable, calculable column. This should have been done when the table was imported by SSIS instead of in the code below.

We also take the opportunity to build a working table with some extra columns to make it easier to calculate and aggregate entry and exit times. Here's the code to build the working table. As always, the details are in the comments. The clustered index applied to this table is absolutely required for the next step to work correctly.

--===== Set the date format for the given data

SET DATEFORMAT DMY

;

--===== Conditionally drop the temp table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#Corrected','U') IS NOT NULL

DROP TABLE #Corrected

;

--===== Create a table with combined dates/times.

-- This should have been done during the SSIS load.

SELECT Pass_M = CAST(PASS_M AS VARCHAR(10)),

Event_DT = CAST(EVENT_D AS DATETIME)

+ CAST(EVENT_T AS DATETIME),

Message_X = CAST(MESSAGE_X AS VARCHAR(6)),

Entry_DT = CAST(NULL AS DATETIME),

Exit_DT = CASE

WHEN MESSAGE_X = 'Exit'

THEN CAST(EVENT_D AS DATETIME)+CAST(EVENT_T AS DATETIME)

ELSE CAST(NULL AS DATETIME)

END

INTO #Work

FROM (--==== This whole section would just be FROM dbo.ACCESS_SOURCE

SELECT 'Staff A','30/04/2012','18:10:00','Exit' UNION ALL

SELECT 'Staff B','01/05/2012','08:30:10','Access' UNION ALL

SELECT 'Staff B','01/05/2012','18:31:20','Exit' UNION ALL

SELECT 'Staff C','30/04/2012','22:00:17','Access' UNION ALL

SELECT 'Staff C','30/04/2012','22:01:28','Access' UNION ALL

SELECT 'Staff C','01/05/2012','05:31:00','Exit' UNION ALL

SELECT 'Staff D','30/04/2012','10:00:00','Access' UNION ALL

SELECT 'Staff D','30/04/2012','16:00:00','Exit' UNION ALL

SELECT 'Staff D','30/04/2012','16:03:00','Exit' UNION ALL

SELECT 'Staff D','01/05/2012','19:50:00','Access' UNION ALL

SELECT 'Staff D','02/05/2012','03:50:00','Exit' UNION ALL

SELECT 'Staff D','02/05/2012','12:00:00','Access' UNION ALL

SELECT 'Staff D','02/05/2012','18:00:00','Exit' UNION ALL

SELECT 'Staff E','02/05/2012','19:00:00','Access'

) source (PASS_M, EVENT_D, EVENT_T, MESSAGE_X)

;

--===== Add the quintessential clustered index for the Quirky Update

CREATE UNIQUE CLUSTERED INDEX IX_#Work_QU

ON #Work (Pass_M, Event_DT)

;

I'm pretty sure that someone could figure this all out just using some prestidigitation with ROW_NUMBER() but I decided to do it a different way. This is known as the "Quirky Update" method and it emulates the same method that you would use if you were doing this in front-end code. It will blow the doors off of any recursive CTE, Cursor, or While loop method that you'll be able to come up with. I must warn you that the method is NOT supported by Microsoft but has been a viable method even in the earliest versions of SQL Server and continues to be viable even in SQL Server 2012. It does not support partitioned tables but the Temp Table takes care of that little problem.

The table and option "hints" are absolutely required.

--===== Declare some obviously named variables just like you would do in the front end

DECLARE @PrevPass_M VARCHAR(10),

@PrevEntry_DT DATETIME,

@PrevMessage_X VARCHAR(6),

@SafetyCounter INT

;

--===== Assuming there's at least one row to be process,

-- preset the safety counter to "1"

SELECT @SafetyCounter = 1

;

--===== "Smear" the "first" entry dates down to other rows.

-- See the embedded comments on the CASE function for details.

WITH

cteSafetyRowNumber AS

( --=== Adds a "counter" to the rows in the expected processing order.

SELECT RowNumber = ROW_NUMBER() OVER (ORDER BY Pass_M, Event_DT),

Pass_M, Event_DT, Message_X, Entry_DT

FROM #Work

)

UPDATE srn

SET @PrevEntry_DT = CASE --This CASE does the safety check

WHEN RowNumber = @SafetyCounter --Checks sequence of processing

THEN

CASE --This CASE does the data "smear"

--== Finds first "Access" after "Exit" for same Pass_M

WHEN Pass_M = @PrevPass_M

AND @PrevMessage_X = 'Exit'

AND Message_X = 'Access'

THEN Event_DT

--== Finds first "Access" for new Pass_M

WHEN Pass_M <> @PrevPass_M

AND Message_X = 'Access'

THEN Event_DT

--== "Data smear" the current date for everything else

ELSE @PrevEntry_DT

END

ELSE 1/0 --Forces an error if out of sequence

END,

--== Setup for next "iteration"

Entry_Dt = @PrevEntry_DT,

@PrevPass_M = Pass_M,

@PrevMessage_X = Message_X,

@SafetyCounter = @SafetyCounter + 1

FROM cteSafetyRowNumber AS srn WITH (TABLOCKX)

OPTION (MAXDOP 1)

;

--===== This will create the final required output

WITH

ctePreAggregate AS

( --=== Find the max exit date for every smeared PASS_M/entry date group.

SELECT Pass_M,

Entry_DT,

Exit_DT = MAX(Exit_Dt)

FROM #Work

WHERE Entry_DT IS NOT NULL

AND Exit_DT IS NOT NULL

GROUP BY Pass_M, Entry_Dt

) --=== This simply calculates the columns that require calculations.

SELECT Pass_M,

Entry_Dt,

Exit_Dt,

Worked_Hrs = CAST(DATEDIFF(hh,0,Exit_DT-Entry_DT) AS VARCHAR(10))

+ ':'

+ SUBSTRING(CONVERT(CHAR(5),DATEADD(ss,30,Exit_DT-Entry_DT),108),4,2),

Consecutive_D = ROW_NUMBER() OVER (PARTITION BY Pass_M ORDER BY Entry_DT)

FROM ctePreAggregate

ORDER BY Pass_M, Entry_DT

;

That produces the requested output as follows.

Pass_M Entry_Dt Exit_Dt Worked_Hrs Consecutive_D

------- ----------------------- ----------------------- ---------- -------------

Staff B 2012-05-01 08:30:10.000 2012-05-01 18:31:20.000 10:01 1

Staff C 2012-04-30 22:00:17.000 2012-05-01 05:31:00.000 7:31 1

Staff D 2012-04-30 10:00:00.000 2012-04-30 16:03:00.000 6:03 1

Staff D 2012-05-01 19:50:00.000 2012-05-02 03:50:00.000 8:00 2

Staff D 2012-05-02 12:00:00.000 2012-05-02 18:00:00.000 6:00 3

[font="Arial Black"]Again and as a reminder, if this is meant to be some form of timekeeping system, there are some serious faults with the logic of the original requirements. Unless door entry and exit can be guaranteed recorded events and that every entry will have one and only one exit, this data must NOT be used for keeping time especially for pay![/font]