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'

Comments

Post a Comment

Popular posts from this blog

Magento/PHP - Get phones on all members in a customer group -

i working on custom magento extension. working around adminhtml part , i've created custom form there. here form code: <?php class vivasindustries_smsnotification_block_adminhtml_sms_sendmass_edit_form extends mage_adminhtml_block_widget_form { public function _preparelayout() { $extensionpath = mage::getmoduledir('js', 'vivasindustries_smsnotification'); $head = $this->getlayout()->getblock('head'); $head->addjs('jquery.js'); $head->addjs('vivas.js'); return parent::_preparelayout(); } protected function _prepareform() { $form = new varien_data_form(array( 'id' => 'edit_form', 'action' => $this->geturl('*/*/save', array('id' => $this->getrequest()->get...
Read more

php - Bypass Geo Redirect for specific directories -

i'm trying bypass redirect specific url within site. wondering if might able help. the code below code use, original urls replaces example.com i'd able access http://example.com/admin without redirect occuring. is there way this, , there way using own ip? function country_geo_redirect() { $country = getenv('http_geoip_country_code'); if ($country == "us" ) { wp_redirect("http://usa.example.com/"); exit; } else if ($country == "gb" ) { wp_redirect("http://uk.example.com/"); exit; } } add_action('init', 'country_geo_redirect'); you can bypass redirection code if server found ip address. if ip address == <my ip address> ' nothing here else country_georedirect() end if
Read more

php - .htaccess mod_rewrite for dynamic url which has domain names -

i want convert url ex:( example.com/link.php?url=facebook.com ) ( example.com/facebook.com.php ) how can using .htaccess. or there other process make job easier ? i tried adding script .htaccess file options +followsymlinks rewriteengine on rewriterule url-(.*)\.htm$ link.php?url=$1 but converting url adding "url-" ( example.com/url-facebook.com.php ) i want remove "url-" , want make ( example.com/facebook.com.php ) rewriteengine on rewritecond %{the_request} ^get.*link\.php\?url\= [nc] rewritecond %{request_uri} !/system/.* [nc] rewriterule (.*?)link\.php\?url\=/*(.*) /$1$2 [r=301,ne,l] not sure how above work edit of used use remove index.php url
Read more