contracting_activity.sql

select	fy_id, asub_name, 
	-- Total Sums		
	sum(r_amount) as totalAmount, 
	sum(dvdollars) as totalDVAmount,
	sum(sbdollars) as totalSBAmount,
	sum(mbdollars) as totalMBAmount	,

	-- Category Sums		
	sum([Disabled Veteran]) as DVCategoryAmount,
	sum([Small Business]) as SBCategoryAmount,
	sum([Micro Business]) as MBCategoryAmount,

	-- Category Percentages
	sum([Disabled Veteran])/sum(dvdollars)*100 as DVPercent, 
	sum([Small Business])/sum(sbdollars)*100 as SBPercent,		
	sum([Micro Business])/sum(mbdollars)*100 as MBPercent
from (
	select 
		-- Determine applicable dollars
		*,
		case when [Disabled Veteran] is null then 0 else [r_amount] end as DVDollars,
		case when [Small Business] is null then 0 else [r_amount] end as SBDollars,
		case when [Micro Business] is null then 0 else [r_amount] end as MBDollars
	from (
		select cur.fy_id, cur.asub_name, cur.ats_id, cur.r_id, cur.r_amount, cur.stat_name, rbpa.rbpa_amount, rbpa.bpty_name
		from ats.vw_r_current cur, ats.vw_rbpa rbpa
		where cur.r_id = rbpa.r_id
		and cur.stat_name in ('Active')
		and rbpa.bpds_name = 'Prime'
	) tblAlias
	pivot (	
		sum(rbpa_amount) 
		FOR bpty_name 
		IN ("Disabled Veteran", "Small Business", "Micro Business") 
	) as pivotTbl
) a
group by asub_name, fy_id