Thread: Breaking down events

- I have looked for assistance on this next question on SQL Server forums but I don't think I will get the results I need without using a 3rd party reporting program (in our case, Crystal Reports). I'm thinking Crystal would work better than a straight SQL query because I need to do calculations of aggregated data.

Here's the question:

I've seen various iterations of this question answered, but haven't seen a way to incorporate it with my data. The problem really is that one record is dependent on a previous record because I'm dealing with calculation of time and mileage throughout the day which is divided into different "activities".
The possible activities are:

Event.Activity
Pull Out
Pick Up
Drop Off
Break
Pull In


Tables
Run
Event

Columns
Run.Date
Run.RunName
Event.Id
Event.Date
Event.RunName
Event.Activity
Event.ArriveTime
Event.DepartTime

Sample Data:

Run.Date....Event.Id....Run.RunName....Event.Date. ...Event.Activity....Event.ArriveTime....Event.Dep artTime
20090901....1...........1001...........20090901... ...Pull Out..........1000................1010
20090901....2...........1001...........20090901... ...Pick Up...........1012................1015
20090901....3...........1001...........20090901... ...Drop Off..........1020................1022
20090901....4...........1001...........20090901... ...Break.............1030................1100
20090901....5...........1001...........20090901... ...Pick Up...........1110................1115
20090901....6...........1001...........20090901... ...Pick Up...........1120................1125
20090901....7...........1001...........20090901... ...Pick Up...........1130................1135
20090901....8...........1001...........20090901... ...Drop Off..........1140................1142
20090901....9...........1001...........20090901... ...Drop Off..........1200................1205
20090901....10..........1001...........20090901... ...Drop Off..........1200................1205
20090901....11..........1001...........20090901... ...Pull In...........1215................1220


I need to be able to do calculations comparing the different parts of the schedule above.
If I only needed to subtract the Event.ArriveTime of the Pull Out from the Event.DepartTime
of the Pull in, it wouldn't be a problem. I would do something like this:

Select E1.ActualDepartTime-E2.ActualArriveTime as 'Sum'
FROM Run R
LEFT OUTER JOIN Event E1
ON R.Date=E1.Date
AND R.RunName=E1.RunName
AND E1.Activity='Pull In'
LEFT OUTER JOIN Event E2
ON R.Date=E2.Date
AND R.RunName=E2.RunName
AND E2.Activity='Pull Out'

WHere R.Date=20090901 AND R.RunName='1001'

Unfortunately, I need to be able to subtract the break period of Event.Id 3 from the total as well as show
the break down of the different time (and mileage in other queries) parts of each schedule on each day or
on date ranges.

Is there a way to aggregate each part of the schedule above where I can do calculations grouping everything
onto one row in the calculation?

So for instance, I would hopefully be able to show the following results:


non revenue=((1012-1000)+(1100-1030)+(1220-1205))=57 minutes or
(1st Pick up arrive -Pull Out Arrive)+(Break depart - Break Arrive)+(Pull In Depart - Last Drop Off depart)

Revenue = all other time...or
((1205-1100)+(1030-1012))=83 minutes

The total time that transpired was 140 minutes but is broken up into revenue and non-revenue time

I'm assuming I probably have to declare values, but would really like to avoid that if possible.
If someone knows of a way to do this either through creative grouping or maybe something like
CTE's etc, that would be wonderful!

Sorry so long, I hope this makes sense!

Edit: DISREGARD...I figured it out using some nested queries and derived tables...thanks guys...I'll migrate the queries over to Crystal and I'm good.


Thanks!