Showing posts with label knowledge. Show all posts
Showing posts with label knowledge. Show all posts

Wednesday, March 28, 2012

query problem (lack of knowledge on my part)

Hello,

I've been trying for a couple of days now with the following problem in SQL

Ive a table called tblRresults that has the following

Date - just a regular date

OfficeLocation - just a unique id such as a zip code

SampleType - contains things like wall, floor, beam

SampleResult - "Detected" or "NotDetected"

When populated the table can look like

12/12/05 99505 Wall Detected

12/12/05 99505 Wall NotDetected

10/04/05 99211 Beam Detected

10/04/05 99211 Beam Detected

10/04/05 99111 Floor NotDetected

10/04/05 99111 Floor NotDetected

What I want is (I guess) a crosstab query that produces the following

Date Location SampleType CountDetected CountNotDetected

12/12/05 99505 Wall 1 1

10/04/05 99211 Beam 2 0 (or null would be ok)

10/04/05 99111 Floor 0 2

Any help gratefully accepted, this has been driving me mental!

thanks

Select Date,Location,SampleType,SUM(CASE WHEN SampleResult='Detected' THEN 1 ELSE 0 END) AS CountDetected,SUM(CASE WHEN SampleResult='NotDetected' THEN 1 ELSE 0 END) AS CountNotDetected

FROM table

GROUP BY Date,Location,SampleType

|||thanks very much! - I had no idea CASE existed

Friday, March 23, 2012

Query Plans differs

Have you tryed updating the statistic ?
Try doing an
exec sp_createstats
exec sp_updatestats
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius

>--Original Message--
>Why shoudl the query plan derived by an SP and one on
Query Analyser would
>differ widely?
>I have a system that has been running stably for a while
now when one of the
>queries started behaving badly. The SP version of the
query comes out with
>an estimated cost of 0.311 whereas if I take the same
TSQL and paste it into
>Query Analyser it estimates as 0.153.
>Unfortuately, the query plan cost isn't the whole story
as when estimated
>the SP performs about 5m page reads whereas the explicit
code only takes 800
>page reads - this makes a big difference in execution
time :-)
>Any ideas how to solve this - I've tried WITH RECOMPILE
on the SP, but it
>doesn't effect the QP choosen.
>Regards
>Paul
>
>.
>Yes, the statistics are updated before the run - thanks for the suggestion
though
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
> Have you tryed updating the statistic ?
> Try doing an
> exec sp_createstats
> exec sp_updatestats
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
>
> Query Analyser would
> now when one of the
> query comes out with
> TSQL and paste it into
> as when estimated
> code only takes 800
> time :-)
> on the SP, but it|||The only other thing I can think of is to delete your sp
then immediatly re-create it, it does sound like your sp
has gone a bit odd.
BTW my other sugestion about updating the stats was
garbage (when I had a chance to think about it)
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain

>--Original Message--
>Yes, the statistics are updated before the run - thanks
for the suggestion
>though
>Paul
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
ignorance."
while
explicit
>
>.
>|||I've tried re-creating the SP a couple of times with no effect - as I said
it's really odd as this procedure has been in production since November and
it started behaving oddly a couple of days ago, with no real change in the
underlying data volume or distribution :-(
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9a01c5200f$5676c630$a601280a@.phx.gbl...
> The only other thing I can think of is to delete your sp
> then immediatly re-create it, it does sound like your sp
> has gone a bit odd.
> BTW my other sugestion about updating the stats was
> garbage (when I had a chance to think about it)
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>
> for the suggestion
> wrote in message
> ignorance."
> while
> explicit