I have a table that looks like this:

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:

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.

CodePudding user response：

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 inbetween BETWEEN previous.`Date` AND 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.

CodePudding user response：

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()