Running the difference in SQL

Question

I have an Excel spreadsheet that easily calculates the current difference using 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

he used to calculate the difference in dates as a kind of grand total. (Obviously, the example is simple, but real-time cells also contain time.)

So, in this example, it’s easy to see that 24 hours were spent in the “Good” state, 24 in the “Bad” state, and the status “Not Good” is still active.

I have an equavalent table in Access that has columns of equipment, date and status. What I would like to do with this is to create a query that does the same thing as the example above. Just the total time in each status to answer the question "How much time did I spend in this status?" The problem is that I’m sure that this will require some kind of sub-query magic, which is on my head. Any ideas?

Thanks in advance.

Answer 1

It really depends on how your data is structured. Is that all that takes place in the table?

The state is always accompanied by the following status, that is, the status with the key n is active before the start of the state using the key n + 1?

, key = key - 1, .

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: . , , diff :

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