I broke this into CTE's because it's easier to see the methodology that way. You could do this all in one query if you wanted. The key is the two subqueries, which are listed as cte and cte2.

Note one of the larger issues is turning your separate date and time stamps into usable values.

/*

if (object_id('tempdb..#Astime') is not null) drop table #Astime

create table #Astime (ResourceId smallint, Site smallint,

DateKey int, TimeKey int, EventType smallint)

insert into #Astime

select 1, 1, 20100901, 80010,2

union

select 1, 1, 20100901, 113001,3

union

select 1, 1, 20100901, 122014,2

union

select 1, 1, 20100901, 153223,3

union

select 1, 1, 20100901, 153814,2

union

select 1, 1, 20100901, 161503,3

union

select 1, 1, 20100902, 80010,2

union

select 1, 1, 20100902, 122014,2

union

select 1, 1, 20100902, 153223,3

union

select 1, 1, 20100902, 153814,2

union

select 1, 1, 20100902, 161510,3

union

select 2, 1, 20100901, 80015,2

union

select 2, 1, 20100901, 113001,3

union

select 2, 1, 20100901, 122014,2

union

select 2, 1, 20100901, 153223,3

union

select 2, 1, 20100901, 153814,2

union

select 2, 1, 20100901, 161523,3

union

select 2, 1, 20100902, 80010,2

union

select 2, 1, 20100902, 122014,2

union

select 2, 1, 20100902, 153223,3

union

select 2, 1, 20100902, 153814,2

union

select 2, 1, 20100902, 161520,3

*/

;WITH cte AS

(SELECT

ResourceID,

DateKey,

MIN( TimeKey) AS StartTime

FROM

#Astime

WHERE

EventType = 2

GROUP BY

ResourceID,

DateKey

)

,cte2 AS

(SELECT

ResourceID,

DateKey,

MAX( TimeKey) AS EndTime

FROM

#Astime

WHERE

EventType = 3

GROUP BY

ResourceID,

DateKey

)

,cte3 AS

(SELECT

c.ResourceID,

c.DateKey,

c.StartTime,

ISNULL( c2.EndTime, 115959) AS EndTime

FROM

cte AS c

LEFT JOIN

cte2 AS c2

Onc.ResourceID = c2.ResourceID

AND c.DateKey = c2.DateKey

)

, cte4 AS

(SELECT

ResourceID,

CONVERT( DateTime, CONVERT( VARCHAR(20), DateKey)) AS DateKey,

DATEADD( mm, DATEDIFF( mm, 0, CONVERT( DATETIME, CONVERT( VARCHAR(20), DateKey))), 0) AS MonthStartDateKey,

--REVERSE( STUFF( STUFF( REVERSE( CONVERT( VARCHAR(30), StartTime)), 3, 0, ':'), 6, 0, ':')) AS test

Convert( Datetime, '1/1/1900 ' + REVERSE( STUFF( STUFF( REVERSE( CONVERT( VARCHAR(30), StartTime)), 3, 0, ':'), 6, 0, ':'))) AS StartTime,

Convert( Datetime, '1/1/1900 ' + REVERSE( STUFF( STUFF( REVERSE( CONVERT( VARCHAR(30), endTime)), 3, 0, ':'), 6, 0, ':'))) AS EndTime

FROM

cte3

)

SELECT

ResourceID,

MonthStartDateKey,

SUM( DATEDIFF(n, StartTime, EndTime)) AS TotalMinutesWorkedInMonth

FROM

cte4

GROUP BY

ResourceID,

MonthStartDateKey