I have one query that runs a SUM statement based on daterange 1, and worked fine, and another query that run same SUM statement, but on a different date range. I would like to merge/combine the two queries in one but I don't know how.
This is my first query:
SELECT
PROD.SezioneID
, PROD.Desc_Prod
, sample.products.VETTURA AS VetturaID
, truncate((SUM(PROD.mtscrap) / SUM(PROD.MtProdotti)) * 100, 2) AS Scrap_Week
, truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_CH_OVER_Week
, truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_ACICLO_Week
FROM flB.flB_prod AS PROD
JOIN FLB.flb_causali CAUSE
ON PROD.SEZIONEID = CAUSE.IDSEZIONE
AND PROD.GIORNO = DATASTARTPRG
JOIN SAMPLE.PRODUCTS
ON (sample.products.SKU = PROD.SEZIONEID)
AND (sample.products.LINEA = 'FLB')
WHERE giorno BETWEEN '2014-06-09' AND '2014-06-13'
GROUP BY SezioneID
The 2nd query is:
SELECT
truncate((SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')))
THEN PROD.mtscrap ELSE 0 END) /
SUM(CASE WHEN ((CAUSE.AVAILABLECAUSA_1 LIKE ('%change%')) )
THEN PROD.MtProdotti ELSE 0 END) * 100 ) , 2
) AS Scrap_ACICLO_Annual
FROM flB.flB_prod AS PROD
JOIN FLB.flb_causali CAUSE
ON PROD.SEZIONEID = CAUSE.IDSEZIONE
AND PROD.GIORNO = DATASTARTPRG
JOIN SAMPLE.PRODUCTS
ON (sample.products.SKU = PROD.SEZIONEID)
AND (sample.products.LINEA = 'FLB')
WHERE ProD.SezioneID IN
(
SELECT SEZIONEID
FROM FLB.flb_prod
WHERE giorno BETWEEN '2014-06-09' AND '2014-06-13'
)
GROUP BY SezioneID
I would like to insert the calculated field Scrap_ACICLO_Annual next to field Scrap_ACICLO_week I hope I explained myself.
(INNER) JOINbetween tables, just before eachgroup by. Have you tried that? – jynus Jul 24 '14 at 14:04