Unir varias consultas em uma so

SQL

24/02/2023

Boa tarde Pessoal,

Estou com varias query e gostaria de saber se tem como uni-las em uma só pois, do jeito que está esta trazendo uma consulta de cada vez e acredito que, se uni-las podemos melhorar ate o desempenho.

Segue.

--******************************[ ESTORNO PARTICULAR ]****************************** --


WITH TMPESTONO (
NOME_IMPOSTO
,MES
,VALOR
)
AS (
SELECT 'ESTORNO PARTICULAR' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = ('DESPESA VIAGEM')

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM


UNION ALL

SELECT 'ESTORNO PARTICULAR' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = 'REFEITORIO'

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM


UNION ALL

SELECT 'ESTORNO PARTICULAR' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = 'CARRO'

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM


UNION ALL

SELECT 'ESTORNO PARTICULAR ' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = 'GASTOSGERAIS'

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM


UNION ALL

SELECT 'ESTORNO PARTICULAR ' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = 'FERIAS'

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM
)

SELECT *

FROM (
SELECT NOME_IMPOSTO
,MES
,VALOR

FROM TMPESTONO
) AS PivotData

PIVOT(SUM(VALOR) FOR MES IN ([F_16], [F_30], [F_40], [F_46], [F_51], [F_52], [F_60], [F_70], [F_72], [F_73], [TOTAL])) AS PivotTable2

ORDER BY 1

--******************************[ ESTORNO SOBRE GREEN VILLIE ]****************************** --


SELECT NOME_IMPOSTO,
[F_16] AS F_16, [F_30] AS F_30, [F_40] AS F_40,
[F_46] AS F_46, [F_51] AS F_51, [F_52] AS F_52,
[F_60] AS F_60, [F_70] AS F_70, [F_72] AS F_72,
[F_73] AS F_73, [F_16]+[F_30]+[F_40]+[F_46]+[F_51]+[F_52]+[F_60]+[F_70]+[F_72]+[F_73] AS TOTAL
FROM
(
SELECT NOME_IMPOSTO, FILFOR, SUM(TOTAL) AS TOTAL FROM
(
SELECT 'ESTORNO '+ SZN.ZN_DESC AS NOME_IMPOSTO, 'F_'+SZN.ZN_ITEM AS FILFOR, SUM((SZN.ZN_PRV*SZN.ZN_PRCAPL)/100) AS TOTAL
FROM
SZN010 SZN
WHERE
SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01','16','30','40','46','51','52','60','70','72','73','80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC ='GRENVILLE'
GROUP BY SZN.ZN_DESC,SZN.ZN_ITEM
)
AS TABELA1
GROUP BY NOME_IMPOSTO, FILFOR
) AS TABELA2
PIVOT (SUM(TOTAL) FOR FILFOR IN ([F_16],[F_30],[F_40],[F_46],[F_51],[F_52],[F_60],[F_70],[F_72],[F_73],[F_74])) P ;


--******************************[ ESTORNO CAVALO ]****************************** --

WITH TMPESTONO (
NOME_IMPOSTO
,MES
,VALOR
)
AS (
SELECT 'ESTORNO CAVALO' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = ('CAVALO')

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM
)

SELECT *

FROM (
SELECT NOME_IMPOSTO
,MES
,VALOR

FROM TMPESTONO
) AS PivotData

PIVOT(SUM(VALOR) FOR MES IN ([F_16], [F_30], [F_40], [F_46], [F_51], [F_52], [F_60], [F_70], [F_72], [F_73], [TOTAL])) AS PivotTable2

ORDER BY 1;


--******************************[ ESTORNO IGREJA ]****************************** --

WITH TMPESTONO (
NOME_IMPOSTO
,MES
,VALOR
)
AS (
SELECT 'ESTORNO IGREJA' AS NOME_IMPOSTO
,'F_' + SZN.ZN_ITEM AS FILFOR
,SUM((SZN.ZN_PRV * SZN.ZN_PRCAPL) / 100) AS TOTAL

FROM SZN010 SZN

WHERE SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01', '16', '30', '40', '46', '51', '52', '60', '70', '72', '73', '80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC = ('IGREJA')

GROUP BY SZN.ZN_DESC
,SZN.ZN_ITEM
)

SELECT *

FROM (
SELECT NOME_IMPOSTO
,MES
,VALOR

FROM TMPESTONO
) AS PivotData

PIVOT(SUM(VALOR) FOR MES IN ([F_16], [F_30], [F_40], [F_46], [F_51], [F_52], [F_60], [F_70], [F_72], [F_73], [TOTAL])) AS PivotTable2

ORDER BY 1;


--******************************[ ESTORNO FAZENDA ]****************************** --
WITH TMPESTONO (NOME_IMPOSTO ,MES,VALOR) AS
(
SELECT 'ESTORNO FAZENDA'AS NOME_IMPOSTO, 'F_'+SZN.ZN_ITEM AS FILFOR, SUM((SZN.ZN_PRV*SZN.ZN_PRCAPL)/100) AS TOTAL
FROM
SZN010 SZN
WHERE
SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01','16','30','40','46','51','52','60','70','72','73','80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC =('EMPRESTIMOS A FAZ')
GROUP BY SZN.ZN_DESC,SZN.ZN_ITEM
)
SELECT
*
FROM
(
SELECT
NOME_IMPOSTO,MES,VALOR
FROM
TMPESTONO
) AS
PivotData
PIVOT
(
SUM(VALOR)
FOR MES IN (
[F_16],[F_30],[F_40],[F_46],[F_51],[F_52],[F_60],[F_70],[F_72],[F_73],[TOTAL])
) AS
PivotTable2
ORDER BY 1 ;

--******************************[ ESTORNO QUAY ]****************************** --

WITH TMPESTONO (NOME_IMPOSTO ,MES,VALOR) AS
(
SELECT 'ESTORNO QUAY'AS NOME_IMPOSTO, 'F_'+SZN.ZN_ITEM AS FILFOR, SUM((SZN.ZN_PRV*SZN.ZN_PRCAPL)/100) AS TOTAL
FROM
SZN010 SZN
WHERE
SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01','16','30','40','46','51','52','60','70','72','73','80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC =('QUAY')
GROUP BY SZN.ZN_DESC,SZN.ZN_ITEM
)
SELECT
*
FROM
(
SELECT
NOME_IMPOSTO,MES,VALOR
FROM
TMPESTONO
) AS
PivotData
PIVOT
(
SUM(VALOR)
FOR MES IN (
[F_16],[F_30],[F_40],[F_46],[F_51],[F_52],[F_60],[F_70],[F_72],[F_73],[TOTAL])
) AS
PivotTable2
ORDER BY 1 ;



--******************************[ ESTORNO VAREJO ]****************************** --
WITH TMPESTONO (NOME_IMPOSTO ,MES,VALOR) AS
(
SELECT 'ESTORNO VAREJO'AS NOME_IMPOSTO, 'F_'+SZN.ZN_ITEM AS FILFOR, SUM((SZN.ZN_PRV*SZN.ZN_PRCAPL)/100) AS TOTAL
FROM
SZN010 SZN
WHERE
SZN.D_E_L_E_T_ = ' '
AND SZN.ZN_ITEM IN ('01','16','30','40','46','51','52','60','70','72','73','80')
AND SZN.ZN_DATA BETWEEN '20230201' AND '20230228'
AND SZN.ZN_DESC =('RETIRADA V')
GROUP BY SZN.ZN_DESC,SZN.ZN_ITEM
) SELECT
*
FROM
(
SELECT
NOME_IMPOSTO,MES,VALOR
FROM
TMPESTONO
) AS
PivotData
PIVOT
(
SUM(VALOR)
FOR MES IN (
[F_16],[F_30],[F_40],[F_46],[F_51],[F_52],[F_60],[F_70],[F_72],[F_73],[TOTAL])
) AS
PivotTable2
ORDER BY 1 ;
Wanderson Fernandes

Wanderson Fernandes

Curtidas 0
POSTAR