sql server - Incorrect SQL Syntax with nested Select statements in Crystal Reports -


trying create crystal report , need make own custom dataset our existing database so. i've done aliasing, checked individual selects solid, when put them together, syntax errors. going wrong? sqlexpress query here:

/*enter date month wish report on: (ex jan 2015 2015-01-xx)*/ select  avgcomprespdays.compdayshigh, avgcomprespdays.compdayslow, avgcomprespdays.respdayshigh, avgcomprespdays.respdayslow, totalwo.total, totalwo.now, totalwo.high, totalwo.low, numbermetgoal.high, numbermetgoal.low, toptentypes.top10 ( /*    select avg(datediff(d,"opendate","clsddate")*1.0) compdaysnow,   avg(datediff(d,"opendate","respondeddate")*1.0) respdaysnow "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'now'  , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),*/  select avg(datediff(d,"opendate","clsddate")*1.0) compdayshigh, avg(datediff(d,"opendate","respondeddate")*1.0) respdayshigh "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'high - 1 day'  , datepart(m,"clsddate") = datepart(m, {?reportingdate}) /*these next 2 lines specify closed tickets in last month*/ , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),  select avg(datediff(d,"opendate","clsddate")*1.0) compdayslow, avg(datediff(d,"opendate","respondeddate")*1.0) respdayslow "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'low - 3 days'  , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ) avgcomprespdays, ( select count("priority") "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'now' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),  select count("priority") high "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'high - 1 day' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),  select count("priority") low "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'low - 3 days' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),  select count("priority") total "trackit_data2"."trackitapp_1"."vtasks_browse" datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ) totalwo, ( select count("priority") high "trackit_data2"."trackitapp_1"."vtasks_browse" datediff(d,"opendate","clsddate") <= 1 , "priority" = 'high - 1 day' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}),  select count("priority") low "trackit_data2"."trackitapp_1"."vtasks_browse" datediff(d,"opendate","clsddate") <= 3 , "priority" = 'low - 3 days' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ) numbermetgoal, ( select top 10 "type",count("type") top10 "trackit_data2"."trackitapp_1"."vtasks_browse" order count("type") desc ) toptentypes

i think you're trying in single query; i'm reading you're trying both single total data points, , collection of top 10 results. these different things , think @ least you'll need break these out separate queries / data sets pulling crystal. it's been awhile since i've worked crystal, if it's ssrs you'd build query , either make stored proc or embed inline , in report software create dataset query. i'm envisioning have dataset top 10 list, , dataset individual total points can place wherever want.

so first take top 10 query , set it's own dataset:

select top 10 "type",count("type") top10 "trackit_data2"."trackitapp_1"."vtasks_browse" order count("type") desc

now individual data points think query can work common table expression (cte). like:

;with avgcomprespdayshigh ( select avg(datediff(d,"opendate","clsddate")*1.0) compdayshigh, avg(datediff(d,"opendate","respondeddate")*1.0) respdayshigh "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'high - 1 day'  , datepart(m,"clsddate") = datepart(m, {?reportingdate}) /*these next 2 lines specify closed tickets in last month*/ , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate})) ), avgcomprespdayslow (select avg(datediff(d,"opendate","clsddate")*1.0) compdayslow, avg(datediff(d,"opendate","respondeddate")*1.0) respdayslow "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'low - 3 days'  , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ), totalwonow ( select count("priority") "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'now' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ), totalwohigh (select count("priority") high "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'high - 1 day' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ), totalwolow (select count("priority") low "trackit_data2"."trackitapp_1"."vtasks_browse" "priority" = 'low - 3 days' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ), totalwototal (select count("priority") total "trackit_data2"."trackitapp_1"."vtasks_browse" datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) ), numbermetgoalhigh ( select count("priority") high "trackit_data2"."trackitapp_1"."vtasks_browse" datediff(d,"opendate","clsddate") <= 1 , "priority" = 'high - 1 day' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate})  ), numbermetgoallow (select count("priority") low "trackit_data2"."trackitapp_1"."vtasks_browse" datediff(d,"opendate","clsddate") <= 3 , "priority" = 'low - 3 days' , datepart(m,"clsddate") = datepart(m, {?reportingdate}) , datepart(yyyy,"clsddate") = datepart(yyyy, {?reportingdate}) )  select  avgcomprespdayshigh.compdayshigh, avgcomprespdayslow.compdayslow, avgcomprespdayshigh.respdayshigh, avgcomprespdayslow.respdayslow, totalwototal.total, totalwonow.now, totalwohigh.high, totalwolow.low, numbermetgoalhigh.high, numbermetgoallow.low avgcomprespdayshigh, avgcomprespdayslow, totalwototal, totalwonow, totalwohigh, totalwolow, numbermetgoalhigh, numbermetgoallow

but still seems pretty clunky me. don't know you're getting field names , passed in variables, if that's crystal reports thing or not. of other comments mentioned removing double quotes, etc.

if data set you're looking single row field each total, might more straightforward make @table variable field each value want, , run each aggregate query updating value in table variable appropriate. select out row , saves of clumsiness of above cte.


Comments

Post a Comment

Popular posts from this blog

javascript - Bootstrap Popover: iOS Safari strange behaviour -

i looking through twitter bootstrap documentation, , i'm interested in implementing dismissible popover when button clicked. here example bootstrap's docs: http://getbootstrap.com/javascript/#dismiss-on-next-click when press "dismissible popover" button os x safari browser works fine. when access same link above ios safari, button not work. is, popover not dismissed on click. why button not working ios safari? the example linked using data-trigger="focus" attribute. try changing data-trigger="click" . <a tabindex="0" class="btn btn-lg btn-danger" role="button" data-toggle="popover" data-trigger="click" title="dismissible popover" data-content="and here's amazing content. it's engaging. right?">dismissible popover</a>
Read more

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

session - Logging Out Using PHP -

i have seen other programs regarding can't seem make work. doing wrong? want achieve after user logged out, going previous pages become restricted. i have 3 php files: 1. sample.php - main page username , password filled in 2. successful.php - user's page after logging in 3. logout.php - destorys session hinder user access pages after logging out sample.php </html> <form action="" method="post"> <table> <tr> <td>username:</td> <td>password:</td> </tr> <tr> <td><input type="text" name="username"></td> <td><input type="password" name="password"></td> </tr> <tr> <td><input type="submit" value="submit" name="submit"></td> </tr> </table> </form> </html> <?php $servername = "localhost"; $username = ...
Read more