I have a table where I query to populate. I want to get a difference between each row.
Lets say
row 1
row 2
i could have a column that = row 2 - row 1 is x
Here is my current code minus that additional col.
SELECT wastewaterlog.t_stamp AS Date_And_Time,
wastewaterlog.GrandTotal AS Totalizer_Read_Gallons,
wastewaterlog.FlowRate / 10 AS Flow_Rate_GPM
FROM wastewaterlog
WHERE wastewaterlog.t_stamp BETWEEN "{Root Container.Start Date.text} 00:00:00" AND "{Root Container.End Date.text} 23:59:59"
i basically want to check totalizer read gallos and indicate how many gallons were added each row.
Got this code to work. But it takes a little bit to load. Any idea how I can speed it up?
SELECT
main.t_stamp AS Date_And_Time,
main.GrandTotal AS Totalizer_Read_Gallons,
main.FlowRate / 10 AS Flow_Rate_GPM,
main.GrandTotal - IFNULL((SELECT sub.GrandTotal
FROM wastewaterlog sub
WHERE sub.t_stamp < main.t_stamp
ORDER BY sub.t_stamp DESC
LIMIT 1), main.GrandTotal) AS Gallons_Change
FROM wastewaterlog main
WHERE main.t_stamp BETWEEN "{Root Container.Start Date.text} 00:00:00" AND "{Root Container.End Date.text} 23:59:59"
ORDER BY main.t_stamp;
SELECT
t_stamp AS Date_And_Time,
GrandTotal AS Totalizer_Read_Gallons,
GrandTotal - LAG(GrandTotal) OVER (ORDER BY t_stamp) AS diff,
FlowRate / 10 AS Flow_Rate_GPM
FROM
wastewaterlog
WHERE
...