i have several years of daily event data in a sqlserver table i would like to stack and chart and get some good stats on each step. data is collected once per day over the course of a couple of hours. Steps have varied over time both in name and number. Also, It may retry starting steps several times, but it always ends with Done. data looks like this:
date status
2012-09-25 08:00:00.0001 Done
2012-09-25 07:30:00.0001 S3
2012-09-25 07:00:00.0001 S2
2012-09-25 06:00:00.0001 S1
2012-09-25 05:30:00.0001 Nope
2012-09-25 05:00:00.0001 S1
2012-09-25 04:31:00.0001 Nope
2012-09-25 04:30:00.0001 S1
2012-09-25 04:01:00.0001 Nope
2012-09-25 04:00:00.0001 S1
2012-09-24 07:00:00.0001 Done
2012-09-24 06:30:00.0001 S3
2012-09-24 06:00:00.0001 S2
2012-09-24 05:00:00.0001 S1
2012-09-24 04:31:00.0001 Nope
2012-09-24 04:30:00.0001 S1
2012-09-24 04:01:00.0001 Nope
2012-09-24 04:00:00.0001 S1
Thats right - two fields. The duration of each step is calculated by the difference in dates in ajacent rows.
by day, i would like to stack the duration of each step and show in a bar chart. further answer step duration average and stddev for weekdays, same stats by Day of week.
Like to ignore the nopes, and just start calculating with the first step after the nope.
Thanks!
... View more