Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
someone help me please? –  gmgiordano Jul 24 at 13:31
    
"inserting" fields next to one another calls for doing an (INNER) JOIN between tables, just before each group by. Have you tried that? –  jynus Jul 24 at 14:04
    
thanks for reply!! Yes, I have tried this, and finally it's all ok, the only one thing is that it's a little slow, CPU meter go on 100% for about 5/6 sec.I have other queries a little more complex of this, but the renspose is more fast like this. Must be more optimized? Thanks again –  gmgiordano Jul 25 at 18:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.