stored procedure syntax help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

stored procedure syntax help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 05:34:26

please see my stored procedure below

my table looks line the following

lane1 20070428 02:02
lane1 20070428 02:15
lane2 20070428 02:16
lane1 20070428 04:02
lane1 20070428 01:02
lane4 20070428 08:02

now what i want to do is return from the stored procedure a recordset with a list of all lanes and there minimum and maximum time

i'm pasting what i did so far but i don't know how to loop through all the lanes and not just do 1

CREATE PROCEDURE spreport 
	-- Add the parameters for the stored procedure here
	@startdate
	@enddate
AS
BEGIN

declare
@lane varchar(10)
@starttime(datetime)
@endtime(datetime)


select @starttime=min(date) from images where lane='lane1' and date>=@startdate and <=@enddate
select @endtime=max(date) from images where lane='lane1' and date>=@startdate and <=@enddate


return


	SET NOCOUNT ON;

    -- Insert statements for procedure here
	



END
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 05:39:12

select @starttime=min(date),
@endtime=max(date)
from images
where lane='lane1' and date >=@startdate and <=@enddate

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 05:39:58

Or, if you want all...

select lane, min(date),
max(date)
from images
where date >=@startdate and <=@enddate
group by lane

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 06:30:00

I find it strange that after more than 500 posts, you have no idea of GROUP BY?
One of the most essential keywords in SQL Server.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 07:02:39

thanks i new group by but i didn't realize how to loop thorugh the stored procedure

i'm trying to use stored procedures now more.

could i do

select @starttime=min(date),
@endtime=max(date)
from images
where lane='lane1' and date >=@startdate and <=@enddate groupby lane

as I will be adding more calculations within the statment?

Kristen
Test

22859 Posts

Posted - 2007-04-30 : 07:06:13

"and date >=@startdate and <=@enddate"

This is a syntax error.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 07:11:22

Also "groupby".
The main problem is that esthera does not even TRY to run the statements to see what is wrong.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 07:47:56

i apologize - i'm going to play around with this and let you know - -
thanks for your help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 07:51:22

You're welcome.
Start with this one posted 04/30/2007 : 05:39:58 .

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-30 : 08:07:59

thanks for you help

I have now revised it to

select lane, min(date) as starttime,
max(date) as endtime
from images
where [date] >=@startdate and [date]<=@enddate
group by lane

which works great

now I also want to get the first name posted(acc to date) for that lane within the date clause and the last name and the total amount of records -- how can i add this in?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-30 : 08:21:33

like this?

Select
	t2.lane, t2.StartTime, t2.EndTime, t1.FirstName, t1.LastName, t2.Total as [Total Records]
From
	SomeTable t1 Join
(
	select lane, min(date) as starttime, max(date) as endtime, count(*) as Total
	from images
	where [date] >=@startdate and [date]<=@enddate 
	group by lane
) t2
On t1.lane = t2.lane and t1.date = starttime

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources