select oacs.ownerId mfr_id, m.mfr_name, u.whitespaceCutoffDate, min( oacs.firstMonth ) firstMonth, max( oacs.lastMonth ) lastMonth from tbl_DW_OperatorAnalyzerClientSummary oacs with (nolock) inner join tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) on u.operatorChoId = oacs.cho_Id inner join tblManufacturers m with (nolock) on m.mfr_id = oacs.ownerId where u.operatorChoId = group by oacs.ownerId, u.whitespaceCutoffDate, m.mfr_name with purchasingByMonth as ( union all select '#mfr_name#' as mfrName, oa.purchasingMonth, sum( oa.forecast_amt ) forecast_amt, 'M' + cast( datediff( month, , oa.purchasingMonth ) + 1 as varchar) as monthCode from tbl_DW_OperatorAnalyzer#_mfrSuffix# oa where oa.cho_id = and oa.purchasingMonth >= group by oa.purchasingMonth ) select mfrName, sum( m1 ) as m1, sum( m2 ) as m2, sum( m3 ) as m3, sum( m4 ) as m4, sum( m5 ) as m5, sum( m6 ) as m6, sum( m7 ) as m7, sum( m8 ) as m8, sum( m9 ) as m9, sum( m10 ) as m10, sum( m11 ) as m11, sum( m12 ) as m12 from ( select * from purchasingByMonth pivot ( sum( forecast_amt ) for monthCode in ( M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12 ) ) p ) xx group by mfrName

#htmleditformat( _r.sql )#