how to avoid a cursor - 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 2000 Forums

SQL Server Development (2000)

how to avoid a cursor

Author

Topic

lfmn
Posting Yak Master

141 Posts

Posted - 2002-01-08 : 13:10:35

I did a search on cursor on this site in the search engine, but did not find an answer to this question. If anyone has an alternate solution, or can point me in the right direction, I would appreciate it.

I'm trying to solve this without using a cursor or a loop. I'm also providing sample data.

I have a table - #tmp which contains a date, an identifier and a value.
I have a table - #week which contains the beginning and ending date for weeks (in my production table the weeks are not always 7 days)
I've included my cursor solution for the results set I'm trying to get (along with the results set).

Can anyone provide an alternate solution? Thanks for your help.

--table to hold values
--drop table #tmp
create table #tmp (date datetime, identifier varchar(20), value int)

insert into #tmp values('01/01/02', 'item', 1)
insert into #tmp values('01/02/02', 'item2', 2)
insert into #tmp values('01/03/02', 'item', 3)
insert into #tmp values('01/04/02', 'item2', 4)
insert into #tmp values('01/05/02', 'item3', 5)
insert into #tmp values('01/06/02', 'item', 6)
insert into #tmp values('01/07/02', 'item2', 7)
insert into #tmp values('01/08/02', 'item', 8)
insert into #tmp values('01/09/02', 'item2', 9)
insert into #tmp values('01/10/02', 'item2', 10)
insert into #tmp values('01/11/02', 'item', 11)
insert into #tmp values('01/12/02', 'item2', 12)
insert into #tmp values('01/13/02', 'item3', 13)
insert into #tmp values('01/14/02', 'item2', 14)

-- table to hold week ends
--drop table #week
create table #week (begin_date datetime, enddate datetime)

insert into #week values('01/01/02','01/07/02')
insert into #week values('01/08/02','01/14/02')

--cursor solution
declare @begin_date datetime
declare @end_date datetime
Declare time_cursor CURSOR FOR

select * from #week

OPEN time_cursor

FETCH NEXT FROM time_cursor into @begin_date, @end_date
WHILE @@FETCH_STATUS = 0
BEGIN

select @end_date as 'date', identifier, sum(value) as 'sum'
from #tmp
where date between @begin_date and @end_date
group by identifier

FETCH NEXT FROM time_cursor into @begin_date, @end_date

END

CLOSE time_cursor
DEALLOCATE time_cursor

RESULTS:
date identifier sum
--------------------------- -------------------- -----------
2002-01-07 00:00:00.000 item 10
2002-01-07 00:00:00.000 item2 13
2002-01-07 00:00:00.000 item3 5

date identifier sum
--------------------------- -------------------- -----------
2002-01-14 00:00:00.000 item 19
2002-01-14 00:00:00.000 item2 45
2002-01-14 00:00:00.000 item3 13

cursors are like hammers - sometimes you have to use them, but watch your thumb!

izaltsman
A custom title

1139 Posts

Posted - 2002-01-08 : 13:41:39

You mean something like this?



SELECT #week.EndDate as [date], identifier , sum(value) as [sum]
FROM 
	#tmp 
	INNER JOIN #week 
		on  #tmp.[date] between #week.begin_date and #week.enddate
GROUP BY EndDate, identifier
ORDER BY ENDDate

lfmn
Posting Yak Master

141 Posts

Posted - 2002-01-08 : 13:49:08

The great ones always make it look easy.

Thanks!!

P.S. I guess I'll have to change my quote

quote:

You mean something like this?



SELECT #week.EndDate as [date], identifier , sum(value) as [sum]
FROM 
	#tmp 
	INNER JOIN #week 
		on  #tmp.[date] between #week.begin_date and #week.enddate
GROUP BY EndDate, identifier
ORDER BY ENDDate

cursors are like hammers - sometimes you have to use them, but watch your thumb!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources