sql server - SQL Query to Get Sum of Program Values using Calculations and Conditions -

i asked question similar earlier don't think gave enough info trying do. have following tables.

program table -          prog_id,          prog_name  unit table -          unit_id,          unit_name,          prog_id         max_beds         max_budgeted-beds         max_unbudgeted-beds         max_escalation-beds  import_data table -          bed_id,          unit_id         ...other unrelated data   bed table -          bed_id,          bed_type_id  bed type table -          bed_type_id,          bed_type 

i trying determine following column headers in output:

occupied beds

--total occupied beds (sum of beds of type 'regular' in import table) (sum(case when bed_type.bed_type_description = 'regular' 1 else 0 end)) 'total occupied'  

budgeted beds

--occupied budgeted beds (if # of 'regular' beds program < max_budgeted_beds sum of 'regular' beds program, if > budgeted value = max_budgeted_beds program)  (sum when < max_budgeted),(case when (sum (case when bed_type.bed_type_description =     'regular' 1 else 0 end)) < unit.unit_beds_max_budgeted (sum (case when bed_type.bed_type_description = 'regular' 1 else 0 end)) else unit.unit_beds_max_budgeted end) 'occupied budgeted beds' 

unbudgeted beds

--occupied unbudgeted beds (if # of 'regular' beds program < max_budgeted_beds 0, if > budgeted value = max_budgeted_beds + max_unbudgeted_beds - # of 'regular' beds program)     , (case when (sum (case when bed_type.bed_type_description = 'regular' 1 else 0 end)) > unit.unit_beds_max_budgeted unit.unit_beds_max_unbudgeted + unit.unit_beds_max_budgeted - (sum (case when bed_type.bed_type_description = 'regular' 1 else 0 end)) else 0 end) 'occupied unbudgeted beds' 

i error in here surrounding reference 'unit.unit_beds_max_budgeted' , i'm not sure why. using logic in different query works output giving me each unit this:

program  unit  occupied_beds   budgeted_beds   unbudgeted_beds --------------------------------------------------------------- med      a2    44              42              2 med      a3    34              32              2 ccc      b2    44              42              2 ccc      b3    34              32              2 

however output trying have med, , ccc, 1 row values rolled (and exclude unit name).

any appreciated, wracking brain , feel should easy can't it. missing necessary field make link? in advance.

your error message telling you need aggregate unbudgeted beds column. it's not enough there aggregates inside case statement. need outer aggregate function.

if re-write this, error should fixed:

--occupied unbudgeted beds (if # of 'regular' beds program < max_budgeted_beds 0, if > budgeted value = max_budgeted_beds + max_unbudgeted_beds - # of 'regular' beds program)     , sum(        case when (sum(case when bed_type.bed_type_description = 'regular' 1 else 0 end)) > unit.unit_beds_max_budgeted                unit.unit_beds_max_unbudgeted + unit.unit_beds_max_budgeted - (sum (case when bed_type.bed_type_description = 'regular' 1 else 0 end))              else 0 end        ) 'occupied unbudgeted beds'