Unir varias consultas em uma so
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 ;
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
Curtidas 0