Complex Query - Part II

I am passing the following query:
select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
else 0 end) as 'Closed',
sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
from ticket where location in (select location from location where
lstate = 'mp')
and received_date between '5/1/2004' and '5/10/2004' group by type
My requirement is for totals of each column generated, suggest. I am
using an automated asp script written by me for generating dynamic sql
reports, for which i just need to give the sql statement, suggest how to
get totals of each column generated automatically if it is of numeric
value and to ignore totals if it is of non numeric value.
Through sql query only.
Part II because my earlier post on May 10, 2004 was not answered.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
On Wed, 16 Jun 2004 23:03:50 -0700, Preet Kanwaljit Singh Shergill wrote:

>
>I am passing the following query:
>select type, count(*) as 'Total', sum(case when status = 'Closed' then 1
>else 0 end) as 'Closed',
>sum(case when status = 'Pending' then 1 else 0 end) as 'Pending',
>sum(case when status = 'Escalated' then 1 else 0 end) as 'Escalated'
>from ticket where location in (select location from location where
>lstate = 'mp')
>and received_date between '5/1/2004' and '5/10/2004' group by type
>My requirement is for totals of each column generated, suggest. I am
>using an automated asp script written by me for generating dynamic sql
>reports, for which i just need to give the sql statement, suggest how to
>get totals of each column generated automatically if it is of numeric
>value and to ignore totals if it is of non numeric value.
>Through sql query only.
>
>Part II because my earlier post on May 10, 2004 was not answered.
>*** Sent via Devdex http://www.devdex.com ***
>Don't just participate in USENET...get rewarded for it!
Hi Preet,
If I understand your question correctly, you can either use this:
SELECT type, COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
GROUP BY type
UNION ALL
SELECT 'Total', COUNT(*) AS 'Total',
COUNT(CASE WHEN status = 'Closed' THEN 1 END) AS 'Closed',
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS 'Pending',
COUNT(CASE WHEN status = 'Escalated' THEN 1 END) AS 'Escalated'
FROM ticket
WHERE location IN (SELECT location FROM location WHERE lstate = 'mp')
AND received_date BETWEEN '20040105' AND '20051005'
(untested)
Or you can look up ROLLUP in Books Online and see if that helps.
Final notes:
1) I changed the ambiguous date format you used to the recommended
YYYYMMDD format. Check if I didn't exchange the DD and MM parts of your
query.
2) I *think* that the subquery for location can be changed to an inner
join, but I'd have to know your table structure to be sure. The inner join
might yield better performance.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks a ton, it does solve my problem.
With this solution my SQL based reporting will be even faster than
before with minimal network traffic.
I no longer have to bother about html lipstick work.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Compilation/sec is more on production where recompilation / sec is zero

Hi Team,

Our production Environment is SQL server 2000 with SP3. I noticed lot of compilation/sec (where recompilation/sec is closed to ZERO). I know every stored procedure will get compiled initially then it may get recompiled for lot many reasons & I have very good understanding of reasons for recompilation & lot of document on that. Where as I am trying to understand what causes for compilation.

I noticed we are not using DBO prefix while calling the stored procedure & also recommended using sp_executesql instead of exec. Is there anything else I need to check?

Is there any document on what causes for compilation other than creating the stored procedure. Once again I am not seeing recompilations.

Any help will be really appreciated.

Thanks

DJ Pallerla

Try these two articles, which you may or may not have already read.

http://support.microsoft.com/?id=263889

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

|||

Thanks Greg for those two articles. I have already read those I am looking for specific article on reasons for compilation.