3
Greetings!
I’m building a query to pick all the times the value was 0 on a given day and the highest time of the previous day.
The data in my database is similar to the data I put in SQL Fiddle. Time_stamp and Value Column. So far, I have built this query to perform the desired query:
SELECT
T1.Time_stamp,
CASE
WHEN T2.Time_Stamp THEN T2.Time_Stamp
END Time_stamp,
CASE
WHEN (T2.Valor = 0) THEN T2.Time_Stamp
WHEN (T1.Valor = 0) THEN T1.Time_Stamp
END AS T2
FROM
(SELECT
Time_Stamp,
Valor,
@seq1:=@seq1 + 1 AS Seq
FROM
test, (SELECT @seq1:=0) r
WHERE
CAST(time_stamp AS DATE) = '2018-11-02'
ORDER BY Time_Stamp DESC) T1
LEFT JOIN
(SELECT
Time_Stamp, Valor,
@seq3:=@seq3 + 1 AS Seq
FROM
test, (SELECT @seq3:=0) r
WHERE
valor = 0.0 and CAST(time_stamp AS DATE) = '2018-11-01'
ORDER BY TIME_STAMP DESC
LIMIT 0,1) T2
ON T1.Seq = T2.Seq
WHERE
T1.valor = 0
The reason I am writing this question is that this same query does not work in my database, but works in SQL Fiddle. I would like to know what the possible reasons are and how to get around that. I would also like to know, if possible, some more efficient alternative to achieve the desired result.
From now on, thank you.