gistfile1.sql

select Lifestyle_cat, replace(replace(short_name, '_males', ''), '_female', '') as short_name,
	   Max(case when WorkOn = 'Good Work' then lifestyle_habits else '' end) as Good_Work_Feedback,
	   Max(case when WorkOn = 'Work on' then lifestyle_habits else '' end) as Work_On_Feedback,
	   Sum(case when WorkOn = 'Good Work' then ResponseCount else 0 end) as Good_Work_Count,
	   Sum(case when WorkOn = 'Work on' then ResponseCount else 0 end) as Work_On_Count

From (
	Select 
	 case
		when q.short_name in ('binge_drinking', 'daily_drinking_female', 'daily_drinking_males')
		then 'Alcohol Consumption'
		else
			case 
				when q.short_name in ('family', 'work')
				then 'Stress & Depression'
				else
					case
						when q.short_name in ('moderate_activity', 'strength_training', 'vigorous_activity')
						then 'Physical Activity'
						else
							case 
								when q.short_name in ('fruits_vegetables', 'high_fat_foods', 'red_meats', 'whole_grains')
								then 'Nutrition'
							end
					end
			end
	end as Lifestyle_cat, q.short_name, a.lifestyle_habits,
	    WorkOn = case when lifestyle_habits_work_column = 0 then 'Work on' else 'Good Work' end,
	    ResponseCount = (
							Select count(sr.user_id) from survey_responses sr join locations lu on sr.user_id = lu.user_id join locations loc on loc.id = lu.id
							where sr.pha_answer_id = a.id and sr.pha_question_id = q.id and sr.lab_date between yr.begin_date and yr.end_date and (@locatioID = 0 or loc.id = @locationID) and (@employerID = 0 or loc.employer_id = @employerID)
						)
	from Report_years yr, pha_questions q join pha_answers a on q.id = a.pha_question_id
	where 
		q.enabled = 1 and a.enabled = 1 and a.lifestyle_habits is not null and lifestyle_habits <> '' and @ReferenceDate between yr.begin_date and yr.end_date
	) rpt
	group by Lifestyle_cat, replace(replace(short_name, '_males', ''), '_female', '')