1
I need to unite 3 darlings with different results and quantities of records in a single query, using the Where end of script as Where main for all darlings. How can I do this?
I tried with the Union, but it does not perform, displaying error about different amount of records.
/*QUERY 1*/
/******QUERY PARA BUSCAR O PERCENTUAL DE PESSOAS NA FABRICA NO PERIODO******/
WITH somatorio AS (
SELECT (CONVERT(DECIMAL(4,2),(SELECT CONVERT(DECIMAL(4,2), COUNT(*)) FROM (SELECT PG_DATA FROM SPG010 WHERE YEAR(PG_DATA) = YEAR(sd3.D3_EMISSAO) AND MONTH(PG_DATA) = MONTH(sd3.D3_EMISSAO) AND PG_MAT = RA_MAT GROUP BY PG_DATA) as dias_trab) /26)) as presenca
FROM SRA010 AS sra
WHERE (RA_SITFOLH NOT IN ('D', 'A') OR RA_DEMISSA > CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR)+CAST(REPLICATE('0', 2 - LEN(MONTH(sd3.D3_EMISSAO))) + RTrim(MONTH(sd3.D3_EMISSAO)) AS VARCHAR)+'31')
AND SUBSTRING(RA_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
)
SELECT SUM(presenca) AS soma FROM somatorio
/*QUERY 2*/
/******QUERY PARA BUSCAR O CENTRO DE CUSTO E EMISSAO******/
SELECT SUBSTRING(ctt.CTT_CUSTO,1,5) AS CTT_CUSTO, CAST(MONTH(sd3.D3_EMISSAO) AS VARCHAR) + '/' + CAST(YEAR(sd3.D3_EMISSAO) AS VARCHAR) AS PERIODO, sd3.D3_EMISSAO,
/*QUERY 3*/
/******SUBQUERY PARA BUSCAR A QUANTIDADE PRODUZIDA NO PERIODO******/
(SELECT sum(sd3_sub1.D3_QUANT)
FROM SD3010 AS sd3_sub1
WHERE sd3_sub1.D3_TM = sd3.D3_TM
AND YEAR(D3_EMISSAO) = YEAR(sd3.D3_EMISSAO) AND MONTH(D3_EMISSAO) = MONTH(sd3.D3_EMISSAO)
AND sd3_sub1.D3_FILIAL = sd3.D3_FILIAL
AND SUBSTRING(sd3_sub1.D3_CC,1,5) = SUBSTRING(sd3.D3_CC,1,5)
AND sd3_sub1.D_E_L_E_T_ <> '*') AS Producao
FROM SD3010 AS sd3
INNER JOIN CTT010 AS ctt ON (SUBSTRING(sd3.D3_CC,1,5) = SUBSTRING(ctt.CTT_CUSTO,1,5))
/*WHERE PRINCIPAL*/
WHERE sd3.D3_TM = '010'
AND sd3.D3_LOCAL IN ('01','02')
AND sd3.D_E_L_E_T_ <> '*'
AND sd3.D3_EMISSAO BETWEEN '20170101' and '20170131'
GROUP BY SUBSTRING(ctt.CTT_CUSTO,1,5), SUBSTRING(sd3.D3_CC,1,5), YEAR(sd3.D3_EMISSAO), MONTH(sd3.D3_EMISSAO), sd3.D3_FILIAL, sd3.D3_TM, sd3.D3_EMISSAO
The result should be something like below, however, only works when I leave the fixed values of Where from Query1, instead of getting the information from Where:
see if temporary tables don’t solve your problem. creates a temporary table, fills it with the result of the selects and then selects it by applying the conditions you need in the WHERE clause. has a look at this article http://imasters.com.br/artigo/3727/sql-server/understandingtabelas-temporarias?trace=1519021197&source=single
– Washington da costa
@Thiagoalessandro: It is to place the result of each query one below the other (vertical concatenation) or to place side by side (horizontal concatenation)?
– José Diz
@Josédiz would be the layout of a standard query: a field next to each other, and the other records just below, putting again a field next to each other
– Thiago Alessandro
@Thiagoalessandro: I could add above, in the description of the topic, sample of how is the result of the 3 queries, individually, and how you want the result at the end?
– José Diz
@Josédiz Atualizado
– Thiago Alessandro