March 24, 2011 at 5:15 pm
have following query
select YEAR, '2', activity_id, CASE [function]
WHEN 'OTH_LAB' THEN 'OLB'
WHEN 'ENG' THEN 'ENG'
WHEN 'MFG' THEN 'MFG'
WHEN 'IWA' THEN 'NLB'
WHEN 'CWA' THEN 'NLB'
WHEN 'BUR' THEN 'NLB'
WHEN 'MTL' THEN 'NLB'
WHEN 'MSC' THEN 'NLB'
WHEN 'MTL_BUR' THEN 'NLB'
ELSE [function]
END AS pool,
sum(hours), sum(dollars)
from dbo.tbACTUALS_DETAIL_WK
where bu_un_gl = 'xx'
group by year, activity_id, [function]
order by year, activity_id, [function]
how do i use POOL as the field on order by statement, as query exists get "duplicate" records
in output due to many to one functions > pool
March 24, 2011 at 8:37 pm
you need to group by the stuff in the case statement...
select YEAR, '2', activity_id, CASE [function]
WHEN 'OTH_LAB' THEN 'OLB'
WHEN 'ENG' THEN 'ENG'
WHEN 'MFG' THEN 'MFG'
WHEN 'IWA' THEN 'NLB'
WHEN 'CWA' THEN 'NLB'
WHEN 'BUR' THEN 'NLB'
WHEN 'MTL' THEN 'NLB'
WHEN 'MSC' THEN 'NLB'
WHEN 'MTL_BUR' THEN 'NLB'
ELSE [function]
END AS pool,
sum(hours), sum(dollars)
from dbo.tbACTUALS_DETAIL_WK
where bu_un_gl = 'xx'
group by year, activity_id, CASE [function]
WHEN 'OTH_LAB' THEN 'OLB'
WHEN 'ENG' THEN 'ENG'
WHEN 'MFG' THEN 'MFG'
WHEN 'IWA' THEN 'NLB'
WHEN 'CWA' THEN 'NLB'
WHEN 'BUR' THEN 'NLB'
WHEN 'MTL' THEN 'NLB'
WHEN 'MSC' THEN 'NLB'
WHEN 'MTL_BUR' THEN 'NLB'
ELSE [function]
END
order by year, activity_id, pool
March 24, 2011 at 10:10 pm
Create a small mapping table for ([function], pool), place all your cases into that table, join it to your query and just GROUP BY pool.
_____________
Code for TallyGenerator
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply