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
ONdrvStart.ResourceID = drvEnd.ResourceID
AND drvStart.DateKey = drvEnd.DateKey
GROUP BY
drvStart.ResourceID,
DATEADD( mm, DATEDIFF( mm, 0, drvStart.DateKey), 0)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA