Firebird-concatenating fields that return from a case
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 I need to concatenate the hours, minutes and seconds field. How do I do this in firebird without using procedure? Also, I couldn't group the same information. Signature Type with econ value.
What I need is Q stay this way: 00:07:48.
0
Author: Leid Santos, 2020-07-16