Calculate The Time To Run SP

Hi All
There is Any Way To Calculate The Time To Run SP Or Select Statement Before
Run It
For Example
SELECT *
FROM stores
WHERE (state = 'CA')
How long Time Take This Query to Run
Thankstry using
SET STATISTICS TIME ON|||No, there are no such facilities in SQL Server. One of the reasons is that t
he optimize can pick
different executing plans, and any estimates based on one execution plan wil
l be totally off if some
other execution plan is selected. You also have the probability of blocking.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...

> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement Befor
e Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||Thank You Fro Reply
But What I Looking For UDF Or SP That Return Elapsed time For Select
Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Return
Just The Time To Calculate This Statement
Because I Have Large data and I want say to the user how many this query
take time
Thanks
"Taha" <taha105@.hotmail.com> wrote in message
news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
> Hi All
> There is Any Way To Calculate The Time To Run SP Or Select Statement
> Before Run It
> For Example
> SELECT *
> FROM stores
> WHERE (state = 'CA')
> How long Time Take This Query to Run
> Thanks
>|||As a maximum you could say them an approximation
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Taha" wrote:

> Thank You Fro Reply
> But What I Looking For UDF Or SP That Return Elapsed time For Select
> Statement I Send To This SP Or UDF Whit out Run it Men Not Need Data Retur
n
> Just The Time To Calculate This Statement
> Because I Have Large data and I want say to the user how many this query
> take time
> Thanks
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:efD9epuVGHA.5364@.tk2msftngp13.phx.gbl...
>
>|||There is no way to find an accurate time for an SP to run. As Tibor Karaszi
had said, the same SP can run for different periods depending on the server
load, configuration, database size and the mood of the SQL Engine :)
You can only find the time it took for the current execution.|||Ok How find the time it took for the current execution Please
Only Time return Parameter I need
Thanks
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
> There is no way to find an accurate time for an SP to run. As Tibor
> Karaszi
> had said, the same SP can run for different periods depending on the
> server
> load, configuration, database size and the mood of the SQL Engine :)
> You can only find the time it took for the current execution.
>|||You have to do this yourself. Either in the client application (declare a va
riable, set it to
current time before execution and after execution check number of ms or s el
apsed), or in the stored
procedure with same basic logic and have an output parm of the procedure whe
re you send out the
number of ms.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Taha" <taha105@.hotmail.com> wrote in message news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...[
color=darkred]
> Ok How find the time it took for the current execution Please
> Only Time return Parameter I need
> Thanks
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:8BA9246F-EC38-491C-B1B6-E7B9B3ADE53D@.microsoft.com...
>[/color]|||Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguNqexVGHA.1536@.TK2MSFTNGP15.phx.gbl...
> You have to do this yourself. Either in the client application (declare a
> variable, set it to current time before execution and after execution
> check number of ms or s elapsed), or in the stored procedure with same
> basic logic and have an output parm of the procedure where you send out
> the number of ms.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Taha" <taha105@.hotmail.com> wrote in message
> news:eRATWOxVGHA.440@.TK2MSFTNGP10.phx.gbl...
>|||You can use profiler also.
Regards
Amish shahsql

Calculate sum of SQL Top 10

I have an SQL statement which returns the Top 10 states with the number of visitors

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

So far this is what I have

state| visitors

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9

But what i need is to calculate the total for the Visitors column in my SQL so that is like so

MD341527.2PA215417.2NJ127510.2NY10258.2VA8136.5MA2922.3FL2562DE2431.9OH2411.9CA2381.9Total Top 10995279.3Total for All Years12555100

I tried using the sum but I was only getting one value and not the rest...So how can i accomplish this?

Thank you

You can play with rollup and cube to get your result. Here is a sample for you to get start:

SELECT

ISNULL(state,'top10'),SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1

GROUP

BY state

WITH

rollup

UNION

ALL

SELECT

'all states',SUM(mycount)as top10Sum,SUM(myavg)as top10avg

FROM

tab1|||

where would i place this query

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

|||

Somehting like this:

SELECT

t3.state, t3.mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM Customer

GROUPBY state

ORDERBYCOUNT(*)DESC) t3

UNION

SELECT

'top10'as state,SUM(t1.mycount1)as mycount1FROM(

SELECT

TOP 10 State,COUNT(*)as mycount1FROM CustomerGROUPBY stateORDERBYCOUNT(*)DESC)as t1

UNION

ALL

SELECT

'ALL',COUNT(customer)as mycount1

FROM

Customer|||

this example you gave me is not working...

basically I need a way to combine the following two SQL statements to have one final result

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

SELECT 'Total Top 10', SUM(t1.Visitors)
FROM
(SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC )t1|||

declare @.result table

(

States varchar(100),

Visitors int

)

insert into @.result (States, Visitors)

SELECT TOP 10 Customer.State States, COUNT(Customer.state) Visitors
FROM [Customer] WHERE Customer.year = '2006'
GROUP BY Customer.state
ORDER BY COUNT(Customer.state) DESC

select *

from @.result

union all

select 'Total Top 10', sum(Visitors) from @.result

|||thanx you're a life saver|||

Hello,

I don't know why it is not working for you since I don't have any data from you to test.

Here is something I used to test the script.

CREATE TABLE [dbo].[tab1$]([state] [nvarchar](50), [customer] [nvarchar](50) )Sample Data:INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c6')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c7')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c8')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c9')INSERT INTO [tab1$] ([state],[customer])VALUES('a1','c10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b1')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b2')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b3')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b4')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b5')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b6')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b7')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b8')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b9')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b10')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b11')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b12')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b13')INSERT INTO [tab1$] ([state],[customer])VALUES('a2','b14')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c1')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c2')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c3')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c4')INSERT INTO [tab1$] ([state],[customer])VALUES('a3','c5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d1')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d2')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d3')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d4')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d5')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d6')INSERT INTO [tab1$] ([state],[customer])VALUES('a4','d7')

Here is the SQL script (I chose top 2 instead):

SELECT t3.state, t3.mycount1FROM(SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC) t3UNION SELECT'top 10'as state,SUM(t1.mycount1)as mycount1FROM (SELECT TOP 2 State,COUNT(*)as mycount1FROM tab1$GROUP BY stateORDER BYCOUNT(*)DESC)as t1UNIONALLSELECT'ALL',COUNT(customer)as mycount1FROM tab1$

|||

When i try to use this @.result table on this query I get the following error

SELECT TOP (10) t1.City City,t1.State State,SUM(t1.Population) Population , SUM(t1.Visitors) Visitors
FROM (
SELECT Customer.zip Zipcode, COUNT(Zipcode) Visitors,Census.city,Census.State,Census.Population
FROM [Customer] JOIN [Census Test Data] Census ON Customer.zip = Census.zipcode
WHERE Customer.month = '8' AND Customer.year = '2006'
GROUP BY Customer.zip, Census.city,Census.State,Census.Population
) t1
GROUP BY t1.city,t1.State
ORDER BY Visitors DESC

ERROR: The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

How do i make the values match

Business Object XI Variable Help

Hi All!

I am creating a report that lists every prescription a person is taking and the "Disease State" it is in. In the Group Header I need to list all the Disease States that this person is taking a prescription for. However, I cannot figure out how to do it.

In the details section, I have a formula for disease state. The person can be taking prescriptions for the same disease states. Therefore, the detail section could look like:
Diabetes
Diabetes
CHF
Diabetes

In the Group Header I need to output:
Diabetes, CHF

Does anyone know how to do this?
Thanks.
DanielleProbably the easiest way is to create a manual running total that will create a string containig unique disease states for each person. Then you can hide details and display person name and disease states in group footer.