I need to merge 3 queries with results and quantities of different records into a single query, using the Where end of the script as where for all queries . How can I do this?
I have tried Union , but it does not run, displaying error on different number 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, but it only works when I leave the Where fixed values of query1, instead of picking up Where:
