Running Difference in SQL

I have an excel spreadsheet that easily calculates a running difference with this formula:

      A             B           C
1    date        status    timeInStatus 
2    12/26/2010  Good      N/A
3    12/27/2010  Bad       24.00      << =(A3-A2)*24
4    12/28/2010  Not-Good  24.00      << =(A4-A3)*24

it's used to calculate the difference in dates as a sort of running total. (obviously the example is simple, but the real date cells contain times as well.)

So in this example it is easy to see that 24 hours was spent in "Good" status,开发者_开发问答 24 in "Bad" status, and "Not-Good" status is still active.

I have an equavalent table in Access that has equipmentId, date, and status columns. What I would like to do with it is create a query that does the same thing as the example above. Just total the time in each status, to answer the question "How much time did I spend in a given status?" Problem is, I'm pretty sure this will require some sort of sub-query magic that's way over my head. Any ideas?

Thanks ahead of time.

---

This really depends on how your data is structured. Is this all that the table holds?

Is the status always followed by the next status, i.e. status with key n is active until the commencement of status with key n + 1?

If this is the case, you need to join the table back to itself on key = key - 1 and then calculate the date differences.

SELECT
    t1.B as Status,
    t1.A as StartDate,
    t2.A as EndDate
FROM
    Table t1 
    LEFT OUTER JOIN Table t2 ON t1.Key = t2.Key - 1

edit: I missed the part about the sum. So you would aggregate the above result on the Status, summing the date diff values like so:

SELECT
    t1.B as Status,
    SUM(DATEDIFF(h, t2.A, t1.A)) as TotalTime
FROM
    Table t1 
    LEFT OUTER JOIN Table t2 ON t1.Key = t2.Key - 1
GROUP BY t1.B