query without using cursor

hi friends,

i want to get that row's startdatetime where sum of duration becomes equal
to or greater than 1000 without using cursor.
create table test
(
duration int,
startdatetime bigint primary key,
userid int
)
go
insert into practise
select 400, 500, 1
union all
select 500, 600, 1
union all
select 100, 650, 1
union all
select 100, 700, 1
go

thnks in adv.,
chakriWould the answer in this case be 650?|||it will be 650 and 700 according to user id|||Sorry - you'll have to explain to me:
What does the user ID have to do with it?
Where does 60 come from?
And why does the answer turn out to be 700?

My reading of the problem was that:
You intend to order the set by startdatetime ASC. Starting from the first record read the duration. If >= 1000 then the first starttime is the result. If not, add the next duration. If >= 1000 then the second starttime is the result. And so on. That's how I got 650 (400 + 500 + 100 = 1000).|||Ok - you edited your typo while I was posting :D

So - why two answers?|||ya sorry i typed wrong.. it should return equal to or greater than 1000. so 650 and 700 are the records as per the data. so it should return those. as i need all the records which lay according to the condition.|||Got you.

Well - the good news is - you don't need to use a cursor.
The bad news is the best you can do is replace it with a loop. Which isn't any better.

There probably is a set based answer to this however I believe that set based solutions to the running total problem don't tend to compare well even to cursors (http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp - I can't but think that the author got a bit confused during his summary though as it doesn't seem to corrolate with his observations).

HTH|||i need to work it.. i am studying your link.. anyway thanks for this and could you help me how to Generate a Fixed length text file as i mean from a table i want specific columns into a .txt file. how to do this. could you guide me.|||What determines the order of the rows for the running total? All of the user id values are 1 in your example, how do you get two answers? I'm pretty sure that there is a set based solution, but I don't understand the problem well enough to solve it at all, much less find a good solution!

-PatP

Query with another query input parameter

Dear Friends,

I have a long query with an input parameter. I want this input parameter be all teh values returned from another query.

SELECT DIR FROM DIRECCAO

BIG QUERY with DIR input parameter.

How can I do?

Thanks.

SELECT @.DIR = DIR FROM DIRECCAO

EXEC BIG_QUERY @.DIR

HTH,

Babu

|||

IT WORKS AND THE QUERY IS:

ALTER PROCEDURE [dbo].[GD_SP_FACTURA_GLOBAL]

AS

DECLARE @.DIR nvarchar(10)

SELECT @.DIR = DIR_NOME FROM Direccao

EXECUTE dbo.GD_SP_FACTURA_ValorTotal @.DIR

BUT How can I SUM all the values returned by the BIGQuery?

THANKS!!

|||How can I have the sum and it's possible to return a list of all values returned by the bigQuery? THANKS!!!|||

Could anyone help me?

Thanks!

Query timeout

Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris Weston
Check for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegr oups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.

> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?

> How about fragmentation of your tables
Good question. I've no idea. How would I find out?

> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.

> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.

> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston
|||Check out query wait option from the sp_configure.

Query timeout

Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_b
ud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.

> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?

> How about fragmentation of your tables
Good question. I've no idea. How would I find out?

> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.

> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.

> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
Chris Weston|||Check out query wait option from the sp_configure.

Query timeout

Now that I've got some sort of DB back, I've come up against an old
problem that my new friends in this group might be able to help me with
:-)
I tend to get a lot of
Fatal error: Maximum execution time of 30 seconds exceeded in
c:\inetpub\wwwroot\syspro\daily_status_bud.php on line 117
in my php scripts, and also in enterprise manager when running certain
(simple) queries. I've got a pretty big db to be fair, about 6 gig,
but is this just a lack of resources? Should I just be throwing memory
at this, or could there be a more nefarious reason for time-outs?
This generally happens when the server is busy, so I'm very much
leaning to a resources and processing beef problem but just wondered if
a more experienced MSSQL mind might think otherwise.
Many thanks
Chris WestonCheck for blocking
Are your statistics up to date?
How about fragmentation of your tables
Are your queries Sargable? Are there a lot of Table or Index scans
instead of Index seeks?
Increase the timeout to 1 minute if you need
This is just a start there is a lot more that you can do
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142955662.623106.137670@.g10g2000cwb.googlegroups.com...
> Check for blocking
Generally I'm not getting many blocking transactions. It does happen
occasionally but I know what to look for.
> Are your statistics up to date?
It's set to auto-update. I guess so, in that case. Is this a big overhead?
Could I do it overnight instead?
> How about fragmentation of your tables
Good question. I've no idea. How would I find out?
> Are your queries Sargable? Are there a lot of Table or Index scans
> instead of Index seeks?
Again, this is something I'm going to have to google. I wouldn't know
whether my queries use the indexes efficiently or not.
> Increase the timeout to 1 minute if you need
That would be nice, but I can't find where to do that.
> This is just a start there is a lot more that you can do
The difference between what I know and what I don't know is pretty enormous.
I can only hope to narrow the gap.
That's already a lot of help, thanks.
--
Chris Weston|||Check out query wait option from the sp_configure.