/*
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
	On	c.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

KS-321165 (12/8/2010)

---

Thanks. I am learning CTE and so it was helpful when you broke it down, but how do you put all this in one query?

SELECT
	drvStart.ResourceID,
	DATEADD( mm, DATEDIFF( mm, 0, drvStart.DateKey), 0) AS MonthStartDateKey,
	SUM( DATEDIFF(n, StartTime, ISNULL( EndTime, CONVERT( DATETIME, '1/1/1900 23:59:59')))) AS TotalMinutesWorkedInMonth
FROM
	
	(SELECT
        ResourceID,
        CONVERT( DateTime, CONVERT( VARCHAR(20), DateKey)) AS DateKey,
        MIN( Convert( Datetime, '1/1/1900 ' + REVERSE( STUFF( STUFF( REVERSE( CONVERT( VARCHAR(30), TimeKey)), 3, 0, ':'), 6, 0, ':')))) AS StartTime
	FROM
			#Astime
	WHERE
			EventType = 2
	GROUP BY
			ResourceID,
			DateKey
	) AS drvStart
	LEFT JOIN
		(SELECT
			ResourceID,
			CONVERT( DateTime, CONVERT( VARCHAR(20), DateKey)) AS DateKey,
			MAX( Convert( Datetime, '1/1/1900 ' + REVERSE( STUFF( STUFF( REVERSE( CONVERT( VARCHAR(30), TimeKey)), 3, 0, ':'), 6, 0, ':')))) AS EndTime
		FROM
				#Astime
		WHERE
				EventType = 3
		GROUP BY
				ResourceID,
				DateKey
		) AS drvEnd
	ON	drvStart.ResourceID = drvEnd.ResourceID
		AND drvStart.DateKey = drvEnd.DateKey
GROUP BY
	drvStart.ResourceID,
	DATEADD( mm, DATEDIFF( mm, 0, drvStart.DateKey), 0)

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

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

REVERSE, STUFF, REPEAT...

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

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

REVERSE, STUFF, REPEAT...

STUFF(STUFF(RIGHT('0' + CONVERT(VARCHAR(6), TimeKey), 6),5,0,':'),3,0,':')