DECLARE @CurrentYear integer SET @CurrentYear = year(getdate()) SELECT g.name, SUM(CASE WHEN i.year_date = @currentyear THEN i.total_inf ELSE 0 END) AS currentyear, SUM(CASE WHEN i.year_date = @currentyear-1 THEN i.total_inf ELSE 0 END) AS previousyear, SUM(CASE WHEN i.year_date = @currentyear-2 THEN i.total_inf ELSE 0 END) AS secpreviousyear, FROM geographic g LEFT OUTER JOIN inforce_state i ON g.geo_code = i.geo_code WHERE g.operations_ind = 'Y' AND g.company_code = '1' AND i.lob_code = '00' AND i.premium_policy_ind = 'P' GROUP BY g.name ORDER BY i.total_inf DESCThat should get you started.