Variable Inside A Nested Loop

I am trying to write a utility/query to get a report from a table. Below
is the some values in the table:
table name: dba_daily_resource_usage_v1
conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch
------------------------------------------------------------
80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:30
80 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:30
45 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:30
95 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30

I need to write a query which for a given date (say 11/15/2004),
generate a resource usage report for a given duration (say 3 days).

Here is my query:
************************************
set quoted_identifier off
declare @var1 int
set @var1=0

--BEGIN OUTER LOOP
while @var1<=3 --INPUT runs the report for 3 days
begin
declare @vstartdate char (10) --INPUT starting date
set @vstartdate='11/15/2004'

--builds a range of date
declare @var2 datetime
set @var2=(select distinct (dateadd(day,@var1,convert(varchar
(10),last_batch,101)))
--set @var2=(select distinct (dateadd(day,@var1,last_batch))
from dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@vstartdate)

set @var1=@var1+1 --increments a day

declare @var5 varchar (12)
--set dateformat mdy

--converts the date into 11/15/2004 format from @var2
set @var5="'"+(convert(varchar(10),@var2,101))+"'"
--print @var5 produces '11/15/2004' as result

declare @vloginame varchar (50)
declare @vdbname varchar (50)

--BEGIN INNER LOOP
declare cur1 cursor read_only for
select distinct loginame,dbname from
dba_daily_resource_usage_v1
where convert(varchar (10),last_batch,101)=@var5
--??????PROBLEM AREA ABOVE STATEMENT??????
--print @var5 produces '11/15/2004' as result
--however cursor is not being built and hence it exits the
--inner loop (cursor)
open cur1
fetch next from cur1 into @vloginame, @vdbname
while @@fetch_status=0
begin
--print @var5 produces '11/15/2004' as result
declare @vl varchar (50)
set @vl="'"+rtrim(@vloginame)+"'"
declare @vd varchar (50)
set @vd="'"+@vdbname+"'"

--processes the cursors
declare @scr varchar (200)
set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 where
loginame="+@vl+
" and dbname="+@vd+" and "+"convert(varchar
(10),last_batch,101)="+@var5)
--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1
where
--loginame=@vloginame and dbname=@vdbname
--and convert(varchar (10),last_batch,101)=@var5)
print @scr
--exec @scr
fetch next from cur1 into @vloginame, @vdbname
end
--END INNER LOOP
select @var2 as "For date"
deallocate cur1
end
--END OUTER LOOP
************************************
PROBLEM:
Even though variable @var5 is being passed as '11/15/2004' inside the
cursor fetch (see print @var5 inside the fetch), the value is not being
used to build the cursor. Hence, the cursor has no row set.
Basically, the variable @var5 is not being processed/passed correctly
from outside the cursor to inside the cursor.

Any help please.
Thanks




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!