SQL Calculate accumulated total but filter out certain rows

Question

I have a table that looks like this:

ID	Date	Transition
45	01/Jan/09	1
23	08/Jan/09	1
12	02/Feb/09	1
77	14/Feb/09	0
39	20/Feb/09	1
33	02/Mar/09	1

I would like a query that returns a running total of the "somedate" column, BUT I want to ignore the transitions from where it goes from "0" to "1" or "0" to "0". (IE: the time between ID: 77 and ID: 39 should not be calculated in the running total). The other transitions ("1" to "0", "1" to "1") should counted as usual.

I want my table to look something like this:

ID	Date	Running_Total
45	01/Jan/09	0
23	08/Jan/09	timeDiff(Jan1, Jan8)
12	02/Feb/09	timeDiff(Jan1, Jan8) + timediff(Jan8, Feb2)
77	14/Feb/09	timeDiff(Jan1, Jan8) + timediff(Jan8, Feb2) + timeDiff(Feb2, Feb14)
39	20/Feb/09	timeDiff(Jan1, Jan8) + timediff(Jan8, Feb2) + timeDiff(Feb2, Feb14) + 0
33	02/Mar/09	timeDiff(Jan1, Jan8) + timediff(Jan8, Feb2) + timeDiff(Feb2, Feb14) + 0 + timeDiff(Feb20, Mar2)

Something like that. I know how to calculate the accumulated cost, but I was wondering if there's any way to get the accumulated cost but also filter it out based on "transitions".

I've tried looking online and on other window functions, but not sure how to incoporate the fact of "looking" at future rows and comparing to initial row with window function.

Any help would be greatly appreciated.

Answer 1

SELECT
SUM(
    CASE
        WHEN previous.Transition = 1 THEN TIMEDIFF(current.`Date`, previous.`Date)
        ELSE 0
    END
)
FROM yourtable current
JOIN yourtable previous
ON previous.`Date` < current.`Date`
LEFT JOIN yourtable inbetween
ON previous.Date < inbetween.Date AND inbetween.Date < current.Date
WHERE inbetween.ID IS NULL

The above adds the difference between current and previous to a grand total (in terms of TIMEDIFF) where previous.Transition is 1 and there is no item between previous and current.

Answer 2

I rather like @LajosArpad solution but here is one way of doing it with window functions -

SELECT *, SUM(`Diff`) OVER (ORDER BY `Date` ASC)
FROM (
    SELECT
        *,
        IF (
            LAG(`Transition`) OVER (ORDER BY `Date` ASC) = 1,
            TIMESTAMPDIFF(DAY, LAG(`Date`) OVER (ORDER BY `Date` ASC), `Date`),
            0
        ) AS `Diff`
    FROM temp
) t;

This assumes that Date contains valid dates ('2009-01-01') and not the strings ('01/Jan/09') you have in your example. I have used TIMESTAMPDIFF() instead of TIMEDIFF()