March 4, 2013 at 1:09 pm
I'm guessing I'm misunderstanding how to use UNION or its results. But here's what I have and the results:
SELECT
a.PLANT_NO AS PlantNumber
,SUM(a.APPLIED_VOL) AS AppliedVolume
FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_SETTLE_FEE] a
GROUP BY a.PLANT_NO
UNION
SELECT
CASE
WHEN a.plant_no = 'ALL' THEN e.[PlantNumber]
ELSE a.plant_no
END AS ResolvedPlantNumber
,SUM(TRANS_VOL) AS AppliedVolume
FROM [EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_RPTS_INVOICE_DTL] a
LEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] e
ON a.ctr_no = e.[ContractNumber]
AND a.PROD_DT BETWEEN e.[PlantEffectiveFromDate] AND e.[PlantEffectiveToDate]
AND a.PROD_DT BETWEEN e.[ContractEffectiveFromDate] AND e.[ContractEffectiveToDate]
AND a.plant_no = 'ALL'
GROUP BY CASE
WHEN a.plant_no = 'ALL' THEN e.[PlantNumber]
ELSE a.plant_no
END
Example result:
PlantNumber --- AppliedVolume
002 --- 324334.00
002 --- 215943448.81
043 --- 3513440.00
043 --- 407522014.65
Is there a way for me to have the plant numbers grouped together so there aren't duplicates?
Such as:
PlantNumber --- AppliedVolume
002 ---- 216,267,782.81
043 --- 411,035,454.65
March 4, 2013 at 1:13 pm
You're good with what you're using UNION for, but you have to keep in mind that UNION vs. UNION ALL is the equivalent of using a DISTINCT or not on the query. It's not grouping/aggregating.
So, for starters, you want to use UNION ALL there because you might otherwise be losing data that you need to feed into your aggregation, unless you can straight dupe rows between the two queries.
Next, what you'll want to do is aggregate the union. It'll basically look like this:
SELECT
SiteCode,
SUM( SomeStuff) AS SumOfStuff
FROM
(SELECT SiteCode, SomeStuff FROM table1
UNION ALL
SELECT SiteCode, SomeStuff From Table2
) AS drv
GROUP By
SiteCode
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 4, 2013 at 1:36 pm
Based on your code, something like this will work:
WITH BaseData as (
SELECT
a.PLANT_NO AS PlantNumber,
SUM(a.APPLIED_VOL) AS AppliedVolume
FROM
[EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_SETTLE_FEE] a
GROUP BY
a.PLANT_NO
UNION ALL
SELECT
CASE WHEN a.plant_no = 'ALL'
THEN e.[PlantNumber]
ELSE a.plant_no
END AS ResolvedPlantNumber,
SUM(TRANS_VOL) AS AppliedVolume
FROM
[EdwStaging].[RAW].[FactSettleFee_TIPS_QRMTIPS_QPOST_RPTS_INVOICE_DTL] a
LEFT OUTER JOIN [EdwStaging].[PSTG].[ContractToPlantMapping] e
ON a.ctr_no = e.[ContractNumber]
AND a.PROD_DT BETWEEN e.[PlantEffectiveFromDate] AND e.[PlantEffectiveToDate]
AND a.PROD_DT BETWEEN e.[ContractEffectiveFromDate] AND e.[ContractEffectiveToDate]
AND a.plant_no = 'ALL'
GROUP BY
CASE WHEN a.plant_no = 'ALL'
THEN e.[PlantNumber]
ELSE a.plant_no
END
)
select
PlantNumber,
sum(AppliedVolume)
from
BaseData
group by
PlantNumber;
March 4, 2013 at 1:53 pm
Thanks Evil Kraig F! Exactly what I needed.
March 4, 2013 at 1:54 pm
And thanks Lynn Pettis! Just saw your post.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply