0
SELECT DP.DATA AS DATA, S.DESCRICAO, COUNT(P.CODIGO) AS QNTD,
(CASE
WHEN (CAST(SUBSTRING(P.HORAFIM FROM 1 FOR 2) AS INTEGER) = CAST(SUBSTRING(P.HORAINI FROM 1 FOR 2) AS INTEGER)) THEN
(CAST(SUBSTRING(P.HORAFIM FROM 1 FOR 2) AS INTEGER) - CAST(SUBSTRING(P.HORAINI FROM 1 FOR 2) AS INTEGER))
ELSE
CAST(0 AS INTEGER)
END) AS HORAS,
(CASE
WHEN (CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER) = CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER)) THEN
CAST(0 AS INTEGER)
WHEN (CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER) > CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER)) THEN
(CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER) - CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER))
ELSE
(CASE
WHEN ( (CAST(SUBSTRING(P.HORAFIM FROM 1 FOR 2) AS INTEGER) > CAST(SUBSTRING(P.HORAINI FROM 1 FOR 2) AS INTEGER))
AND (CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER) > CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER)) ) THEN
((CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER)+ 60) - CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER))
ELSE
(CAST(SUBSTRING(P.HORAFIM FROM 4 FOR 2) AS INTEGER) - CAST(SUBSTRING(P.HORAINI FROM 4 FOR 2) AS INTEGER))
END)
END) AS MINUTOS,
(CASE
WHEN (CAST(SUBSTRING(P.HORAFIM FROM 7 FOR 2) AS INTEGER) = CAST(SUBSTRING(P.HORAINI FROM 7 FOR 2) AS INTEGER)) THEN
CAST(0 AS INTEGER)
WHEN (CAST(SUBSTRING(P.HORAFIM FROM 7 FOR 2) AS INTEGER) > CAST(SUBSTRING(P.HORAINI FROM 7 FOR 2) AS INTEGER)) THEN
(CAST(SUBSTRING(P.HORAFIM FROM 7 FOR 2) AS INTEGER) - CAST(SUBSTRING(P.HORAINI FROM 7 FOR 2) AS INTEGER))
ELSE
(CAST(SUBSTRING(P.HORAINI FROM 7 FOR 2) AS INTEGER) - CAST(SUBSTRING(P.HORAFIM FROM 7 FOR 2) AS INTEGER))
END) AS SEGUNDOS
FROM DET_PEDIDO DP
INNER JOIN PEDIDO P ON P.PEDIDO = DP.PEDIDO AND P.DATAINI = DP.DATA
INNER JOIN SERVICOS S ON S.CODSERV = DP.CODSERV
WHERE P.DATAINI between '02.01.2020' and '02.01.2020'
I have this query in SQL and need to concatenate the field hours, minutes and seconds. How do I do this in Firebird without using Procedure? Also, I could not group the same information. Type Firm with Econ value.
What I need is to look like this: 00:07:48.
It seems to me you want to calculate the difference between
HORAFIMandHORAINICIO. In this case, assuming your fields are strings, it is not enough to do: CAST(HORAFIM AS time) - CAST(horainicio AS time) to get the interval in seconds and, if applicable, convert to hour:minute:second? Or use the DATEDIFF function()?– anonimo
You could not use the DATEDIFF() function because Firebird is 1.5. The fields were not string. I recolvi passing the fields q were integer to String and then did a Cast with everything.
– Leid Santos
I honestly didn’t understand it. How were your fields what they meant? An integer representing what? If they weren’t strings why do you substring?
– anonimo
The fields were of type datetime and needed to calculate the time q the client took to be served. I used SUBSTRING to pick only the time. I did the same for mm and ss and so subtracted the initial time from the final time. After that, I cast to return the concatenated fields (Ex.: 00:10:09).
– Leid Santos