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_resou rce_usage_v1
conn|loginame|d bname|cum_cpu|c um_io|cum_mem|l ast_batch
------------------------------------------------------------
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_identifi er 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,@v ar1,convert(var char
(10),last_batch ,101)))
--set @var2=(select distinct (dateadd(day,@v ar1,last_batch) )
from dba_daily_resou rce_usage_v1
where convert(varchar (10),last_batch ,101)=@vstartda te)
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="'"+(conv ert(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_resou rce_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="'"+@vdbnam e+"'"
--processes the cursors
declare @scr varchar (200)
set @scr=("select max(cum_cpu) from dba_daily_resou rce_usage_v1 where
loginame="+@vl+
" and dbname="+@vd+" and "+"convert(varc har
(10),last_batch ,101)="+@var5)
--set @var3 =(select max(cum_cpu) from dba_daily_resou rce_usage_v1
where
--loginame=@vlogi name 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!
