SELECT DA TELA
WITH QueryParametro AS
(SELECT
regexp_split_to_table(hs_parametros.valorparametro,',')::INTEGER AS usuarios
FROM hs_parametros
WHERE hs_parametros.nomeparametro = 'HS_parametro_marcadesmarca_diatrabalhado'
)
,QueryDados AS
(SELECT
hs_veiculo_marcador.veiculo
,hs_veiculo_marcador.dtdatabase::DATE AS dtdatabase
,hs_veiculo_marcador.diatrabalhado
,hs_veiculo_marcador.dtdatabase AS data_pk
FROM hs_veiculo_marcador
LEFT JOIN LATERAL
(SELECT
(fnc.camposretorno ->> 'frota')::INTEGER AS frota
,(fnc.camposretorno ->> 'carreta1')::VARCHAR AS carreta1
,(fnc.camposretorno ->> 'carreta2')::VARCHAR AS carreta2
,(fnc.camposretorno ->> 'proprietario_nome')::VARCHAR AS proprietario_nome
,(fnc.camposretorno ->> 'atrelamento')::VARCHAR AS atrelamento
,(fnc.camposretorno ->> 'tipofrotadescricao')::VARCHAR AS tipofrotadescricao
,(fnc.camposretorno ->> 'tipofrotacodigo')::INTEGER AS tipofrotacodigo
,(fnc.camposretorno ->> 'operacao_cod')::INTEGER AS operacao_cod
,(fnc.camposretorno ->> 'operacao')::VARCHAR AS operacao
FROM fnc_hs_busca_operacao_tipofrota
(hs_veiculo_marcador.veiculo
,CURRENT_DATE
) AS fnc
) AS operacao_tipofrota
ON TRUE
WHERE hs_veiculo_marcador.dtdatabase::DATE >= '2024-11-01'::DATE
AND hs_veiculo_marcador.dtdatabase::DATE <= '2024-11-30'::DATE
AND (operacao_tipofrota.operacao_cod = NULL OR COALESCE(NULL,0) = 0)
AND (hs_veiculo_marcador.veiculo = 'ADM9999' OR COALESCE('ADM9999','') = '')
--AND hs_veiculo_marcador.veiculo <> 'ADM9999'
)
,QueryFinal AS
(SELECT
QueryDados.veiculo
,(TO_CHAR(QueryDados.dtdatabase,'DD/MM')||CHR(13)||retorno.dianome)::VARCHAR AS dtdatabase
,QueryDados.diatrabalhado
,QueryDados.data_pk AS dia
-- ,QueryDados.dtdatabase
FROM QueryDados
LEFT JOIN LATERAL
(SELECT
tipodia.DiaSemana
,(CASE WHEN feriado.sequencia IS NOT NULL THEN
'Fer'
ELSE
(CASE WHEN tipodia.DiaSemana = 0 THEN
'Dom'
WHEN tipodia.DiaSemana = 1 THEN
'Seg'
WHEN tipodia.DiaSemana = 2 THEN
'Ter'
WHEN tipodia.DiaSemana = 3 THEN
'Qua'
WHEN tipodia.DiaSemana = 4 THEN
'Qui'
WHEN tipodia.DiaSemana = 5 THEN
'Sex'
WHEN tipodia.DiaSemana = 6 THEN
'Sáb'
END)
END) AS dianome
,feriado.sequencia
FROM (SELECT
EXTRACT( DOW FROM QueryDados.dtdatabase) AS DiaSemana
) AS tipodia
LEFT JOIN feriado
ON feriado.dt = QueryDados.dtdatabase
) AS retorno
ON TRUE
ORDER BY
QueryDados.veiculo ASC
,QueryDados.dtdatabase ASC
)
SELECT
DISTINCT
QueryFinal.veiculo
,dia01.dtdatabase AS dt01
,(CASE WHEN dia01.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia01.diatrabalhado::BOOLEAN
WHEN dia01.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia01.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab01
,dia01.dia AS dtdia01
,dia02.dtdatabase AS dt02
-- ,dia02.diatrabalhado::BOOLEAN AS diatrab02
,(CASE WHEN dia02.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia02.diatrabalhado::BOOLEAN
WHEN dia02.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia02.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab02
,dia02.dia AS dtdia02
,dia03.dtdatabase AS dt03
-- ,dia03.diatrabalhado::BOOLEAN AS diatrab03
,(CASE WHEN dia03.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia03.diatrabalhado::BOOLEAN
WHEN dia03.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia03.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab03
,dia03.dia AS dtdia03
,dia04.dtdatabase AS dt04
-- ,dia04.diatrabalhado::BOOLEAN AS diatrab04
,(CASE WHEN dia04.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia04.diatrabalhado::BOOLEAN
WHEN dia04.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia04.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab04
,dia04.dia AS dtdia04
,dia05.dtdatabase AS dt05
-- ,dia05.diatrabalhado::BOOLEAN AS diatrab05
,(CASE WHEN dia05.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia05.diatrabalhado::BOOLEAN
WHEN dia05.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia05.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab05
,dia05.dia AS dtdia05
,dia06.dtdatabase AS dt06
-- ,dia06.diatrabalhado::BOOLEAN AS diatrab06
,(CASE WHEN dia06.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia06.diatrabalhado::BOOLEAN
WHEN dia06.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia06.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab06
,dia06.dia AS dtdia06
,dia07.dtdatabase AS dt07
-- ,dia07.diatrabalhado::BOOLEAN AS diatrab07
,(CASE WHEN dia07.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia07.diatrabalhado::BOOLEAN
WHEN dia07.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia07.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab07
,dia07.dia AS dtdia07
,dia08.dtdatabase AS dt08
-- ,dia08.diatrabalhado::BOOLEAN AS diatrab08
,(CASE WHEN dia08.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia08.diatrabalhado::BOOLEAN
WHEN dia08.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia08.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab08
,dia08.dia AS dtdia08
,dia09.dtdatabase AS dt09
-- ,dia09.diatrabalhado::BOOLEAN AS diatrab09
,(CASE WHEN dia09.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia09.diatrabalhado::BOOLEAN
WHEN dia09.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia09.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab09
,dia09.dia AS dtdia09
,dia10.dtdatabase AS dt10
-- ,dia10.diatrabalhado::BOOLEAN AS diatrab10
,(CASE WHEN dia10.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia10.diatrabalhado::BOOLEAN
WHEN dia10.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia10.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab10
,dia10.dia AS dtdia10
,dia11.dtdatabase AS dt11
-- ,dia11.diatrabalhado::BOOLEAN AS diatrab11
,(CASE WHEN dia11.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia11.diatrabalhado::BOOLEAN
WHEN dia11.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia11.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab11
,dia11.dia AS dtdia11
,dia12.dtdatabase AS dt12
-- ,dia12.diatrabalhado::BOOLEAN AS diatrab12
,(CASE WHEN dia12.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia12.diatrabalhado::BOOLEAN
WHEN dia12.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia12.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab12
,dia12.dia AS dtdia12
,dia13.dtdatabase AS dt13
-- ,dia13.diatrabalhado::BOOLEAN AS diatrab13
,(CASE WHEN dia13.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia13.diatrabalhado::BOOLEAN
WHEN dia13.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia13.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab13
,dia13.dia AS dtdia13
,dia14.dtdatabase AS dt14
-- ,dia14.diatrabalhado::BOOLEAN AS diatrab14
,(CASE WHEN dia14.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia14.diatrabalhado::BOOLEAN
WHEN dia14.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia14.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab14
,dia14.dia AS dtdia14
,dia15.dtdatabase AS dt15
-- ,dia15.diatrabalhado::BOOLEAN AS diatrab15
,(CASE WHEN dia15.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia15.diatrabalhado::BOOLEAN
WHEN dia15.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia15.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab15
,dia15.dia AS dtdia15
,dia16.dtdatabase AS dt16
-- ,dia16.diatrabalhado::BOOLEAN AS diatrab16
,(CASE WHEN dia16.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia16.diatrabalhado::BOOLEAN
WHEN dia16.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia16.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab16
,dia16.dia AS dtdia16
,dia17.dtdatabase AS dt17
-- ,dia17.diatrabalhado::BOOLEAN AS diatrab17
,(CASE WHEN dia17.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia17.diatrabalhado::BOOLEAN
WHEN dia17.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia17.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab17
,dia17.dia AS dtdia17
,dia18.dtdatabase AS dt18
-- ,dia18.diatrabalhado::BOOLEAN AS diatrab18
,(CASE WHEN dia18.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia18.diatrabalhado::BOOLEAN
WHEN dia18.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia18.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab18
,dia18.dia AS dtdia18
,dia19.dtdatabase AS dt19
--,dia19.diatrabalhado::BOOLEAN AS diatrab19
,(CASE WHEN dia19.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia19.diatrabalhado::BOOLEAN
WHEN dia19.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia19.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab19
,dia19.dia AS dtdia19
,dia20.dtdatabase AS dt20
--,dia20.diatrabalhado::BOOLEAN AS diatrab20
,(CASE WHEN dia20.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia20.diatrabalhado::BOOLEAN
WHEN dia20.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia20.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab20
,dia20.dia AS dtdia20
,dia21.dtdatabase AS dt21
--,dia21.diatrabalhado::BOOLEAN AS diatrab21
,(CASE WHEN dia21.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia21.diatrabalhado::BOOLEAN
WHEN dia21.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia21.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab21
,dia21.dia AS dtdia21
,dia22.dtdatabase AS dt22
-- ,dia22.diatrabalhado::BOOLEAN AS diatrab22
,(CASE WHEN dia22.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia22.diatrabalhado::BOOLEAN
WHEN dia22.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia22.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab22
,dia22.dia AS dtdia22
,dia23.dtdatabase AS dt23
--,dia23.diatrabalhado::BOOLEAN AS diatrab23
,(CASE WHEN dia23.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia23.diatrabalhado::BOOLEAN
WHEN dia23.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia23.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab23
,dia23.dia AS dtdia23
,dia24.dtdatabase AS dt24
-- ,dia24.diatrabalhado::BOOLEAN AS diatrab24
,(CASE WHEN dia24.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia24.diatrabalhado::BOOLEAN
WHEN dia24.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia24.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab24
,dia24.dia AS dtdia24
,dia25.dtdatabase AS dt25
-- ,dia25.diatrabalhado::BOOLEAN AS diatrab25
,(CASE WHEN dia25.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia25.diatrabalhado::BOOLEAN
WHEN dia25.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia25.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab25
,dia25.dia AS dtdia25
,dia26.dtdatabase AS dt26
-- ,dia26.diatrabalhado::BOOLEAN AS diatrab26
,(CASE WHEN dia26.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia26.diatrabalhado::BOOLEAN
WHEN dia26.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia26.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab26
,dia26.dia AS dtdia26
,dia27.dtdatabase AS dt27
-- ,dia27.diatrabalhado::BOOLEAN AS diatrab27
,(CASE WHEN dia27.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia27.diatrabalhado::BOOLEAN
WHEN dia27.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia27.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab27
,dia27.dia AS dtdia27
,dia28.dtdatabase AS dt28
-- ,dia28.diatrabalhado::BOOLEAN AS diatrab28
,(CASE WHEN dia28.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia28.diatrabalhado::BOOLEAN
WHEN dia28.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia28.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab28
,dia28.dia AS dtdia28
,dia29.dtdatabase AS dt29
-- ,dia29.diatrabalhado::BOOLEAN AS diatrab29
,(CASE WHEN dia29.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia29.diatrabalhado::BOOLEAN
WHEN dia29.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia29.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab29
,dia29.dia AS dtdia29
,dia30.dtdatabase AS dt30
-- ,dia30.diatrabalhado::BOOLEAN AS diatrab30
,(CASE WHEN dia30.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia30.diatrabalhado::BOOLEAN
WHEN dia30.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia30.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab30
,dia30.dia AS dtdia30
,dia31.dtdatabase AS dt31
-- ,dia31.diatrabalhado::BOOLEAN AS diatrab31
,(CASE WHEN dia31.dia >= (CURRENT_DATE - INTERVAL '2 DAY')::DATE THEN
dia31.diatrabalhado::BOOLEAN
WHEN dia31.dia < (CURRENT_DATE - INTERVAL '2 DAY')::DATE AND liberacao.possui = 1 THEN
dia31.diatrabalhado::BOOLEAN
ELSE
NULL::BOOLEAN
END) AS diatrab31
,dia31.dia AS dtdia31
FROM QueryFinal
LEFT JOIN LATERAL
(SELECT
COUNT(QueryParametro.usuarios) AS possui
FROM QueryParametro
WHERE QueryParametro.usuarios = 430
) AS liberacao
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '01'
) AS dia01
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '02'
) AS dia02
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '03'
) AS dia03
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '04'
) AS dia04
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '05'
) AS dia05
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '06'
) AS dia06
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '07'
) AS dia07
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '08'
) AS dia08
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '09'
) AS dia09
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '10'
) AS dia10
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '11'
) AS dia11
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '12'
) AS dia12
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '13'
) AS dia13
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '14'
) AS dia14
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '15'
) AS dia15
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '16'
) AS dia16
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '17'
) AS dia17
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '18'
) AS dia18
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '19'
) AS dia19
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '20'
) AS dia20
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '21'
) AS dia21
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '22'
) AS dia22
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '23'
) AS dia23
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '24'
) AS dia24
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '25'
) AS dia25
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '26'
) AS dia26
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '27'
) AS dia27
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '28'
) AS dia28
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '29'
) AS dia29
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '30'
) AS dia30
ON TRUE
LEFT JOIN LATERAL
(SELECT
qry.dtdatabase
,qry.diatrabalhado
,qry.dia
FROM QueryFinal qry
WHERE qry.veiculo = QueryFinal.veiculo
AND TO_CHAR(qry.dia,'DD') = '31'
) AS dia31
ON TRUE
-- WHERE QueryFinal.veiculo = 'ADM9999' ---
SCRIPT DO UPDATE
DO LANGUAGE plpgsql
$body$
DECLARE
rRetorno tp_fnc_hs_iae;
tTabela hs_veiculo_marcador;
cCampos TEXT;
cWhere TEXT;
rRetorno_diatrabalhado tp_fnc_hs_marcadesmarca_diatrabalhado;
BEGIN
--Buscar os valores default da tabela.
tTabela = NULL;
cCampos = NULL;
cWhere = NULL;
/************* BUSCA DADOS *****************/
SELECT
hs_veiculo_marcador.*
INTO
tTabela
FROM hs_veiculo_marcador
WHERE hs_veiculo_marcador.veiculo = 'ADM9999'
AND hs_veiculo_marcador.dtdatabase::DATE = '28/11/2024 02:59:59'::DATE;
/************* INCLUI VALORES DOS CAMPOS *****************/
cCampos = '{"motorista" : '|| COALESCE(TO_JSON(TRIM('27234162836')::TEXT),'null') ||'}';
/************* CHAMA WHERE *****************/
cWhere = '{"veiculo" : '|| COALESCE(TO_JSON(TRIM('ADM9999')::TEXT),'null') ||'
,"dtdatabase" : '|| COALESCE(TO_JSON(TRIM('28/11/2024 02:59:59')::TEXT),'null') ||'}';
/************* CHAMA ALTERAÇÃO *****************/
SELECT
returninc.*
INTO
rRetorno
FROM fnc_hs_iae('A'
,430
,1
,1
,1
,1
,tTabela
,'hs_veiculo_marcador'
,cCampos
,cWhere
,NULL) AS returninc;
IF rRetorno.erro THEN
RAISE EXCEPTION '%',rRetorno.mensagem;
END IF;
SELECT
*
INTO
rRetorno_diatrabalhado
FROM fnc_hs_marcadesmarca_diatrabalhado(430
,1
,1
,1
,1
,'ADM9999'
,'28/11/2024 02:59:59'::DATE
);
END;
$body$