July 9, 2003 at 1:39 pm
I am having troubles getting to the else statement in this
cursor. I am not really familiar with cursors, but I was
told this is the only way to get the information that I
want.
This is the data listed in the same order as the sursor
selects it
07/06/2003 13:080123691236933633
336331158815125412335673563
913525001234567899873
07/06/2003 13:470102708102708451334
4513341022811275071239278124587462
13500274959566011442734352559421201
Cursor text
set nocount off
Declare DMSexportcursor Insensitive cursor for
Select convert(varchar(10),workdate,101), totaldownloadvolume, total_workvolume, strategyvolume, totaldials, dialattemps,
accountscalled, dialerconnects, contacts, promisestopay, signedinduration, answers, abandons
allocatedtime, availabletime, wraptime, talktime
from tbl_report
where report_id in (1,3)
Open Dmsexportcursor
Fetch next from Dmsexportcursor
While @@Fetch_Status = 0
Begin
If (select sum(strategyvolume) from tbl_report where report_id = 1 ) >'0'
Begin
select distinct convert (varchar(10), a.workdate, 101) as workdate, a.total_workvolume, a.strategyvolume,
(a.totaldials + b.totaldials) as totaldials,
cast(((a.totaldials + b.totaldials)/cast((a.strategyvolume + b.strategyvolume) as decimal (9,2))*100)as decimal (9,2)) as penetration,
a.accountscalled,(a.dialerconnects + b.dialerconnects)as dialerconnects,
cast (((a.totaldials +b.totaldials)/cast ((a.dialerconnects +b.dialerconnects) as decimal (9,2))*10)as decimal (9,2))as connectspercent,
(a.contacts + b.contacts) as contacts,
cast(((a.contacts + b.contacts)/cast((a.dialerconnects + b.dialerconnects) as decimal (9,2))*100) as decimal (9,2))as contactpercent,
cast(((a.dialerconnects +b.dialerconnects)/cast((a.totaldials + b.totaldials) as decimal(9,2))*10)as decimal (9,2)) as RPCtodial,
(a.promisestopay + b.promisestopay) as promisestopay,
cast ((a.promisestopay + (b.promisestopay)/cast((a.contacts + b.contacts) as decimal (9,2))*100) as decimal (9,2))as ptppercent,
cast (((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))as signedinduration,
(a.strategyvolume + b.strategyvolume)/(cast (((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))) as accountsperhour,
(a.dialerconnects + b.dialerconnects) /(cast (((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))) as connectsperhour,
(a.promisestopay + b.promisestopay)/(cast (((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))) as ptpperhour,
(a.contacts + b.contacts)/cast (((a.accountscalled + b.accountscalled))as decimal (9,2))*100 as contactpercent,
(a.contacts + b.contacts)/(cast(((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))) as contactsperhour,
(a.totaldials + b.totaldials) / (cast (((a.signedinduration + b.signedinduration)/cast (3600 as decimal (9,2))) as decimal (9,2))) as dialsperhour,
(a.answers + b.answers)as answers, (a.abandons + b.abandons) as abandons,
(a.abandons + b.abandons) / cast (((a.answers + b.answers)) as decimal (9,2))*100 as abandonspercent,
(a.allocatedtime + b.allocatedtime) as allocatedtime, (a.availabletime + b.availabletime) as availabletime,
(a.wraptime + b.wraptime) as wraptime, (a.talktime + b.talktime) as talktime
from tbl_report a, vw_sfrefresh b
where a.report_id = 1 and b.report_id = 3 and
convert (varchar(10),a.workdate,101) = convert (varchar(10),b.workdate,101)
end
else if (select sum(totaldials) from tbl_report where report_id = 3 ) <>0
Begin
select distinct convert (varchar(10), workdate, 101) as workdate, total_workvolume, strategyvolume, totaldials,
cast((totaldials/cast(strategyvolume as decimal (9,2))*100)as decimal (9,2)) as penetration,
accountscalled,dialerconnects,
cast ((totaldials/cast (dialerconnects as decimal (9,2))*10)as decimal (9,2))as connectspercent,
contacts, cast((contacts/cast(dialerconnects as decimal (9,2))*100) as decimal (9,2))as contactpercent,
cast((dialerconnects/cast(totaldials as decimal(9,2))*10)as decimal (9,2)) as RPCtodial,
promisestopay,
cast ((promisestopay/cast(contacts as decimal (9,2))*100) as decimal (9,2))as ptppercent,
cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))as signedinduration,
strategyvolume/(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as accountsperhour,
dialerconnects /(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as connectsperhour,
promisestopay/(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as ptpperhour,
contacts/cast ((accountscalled)as decimal (9,2))*100 as contactpercent,
contacts/ (cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as contactsperhour,
totaldials / (cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as dialsperhour,
answers, abandons, abandons / cast ((answers) as decimal (9,2))*100 as abandonspercent,
allocatedtime, availabletime, wraptime, talktime
from tbl_report
where report_id = 1
end
else
select distinct convert (varchar(10), workdate, 101) as workdate, total_workvolume, strategyvolume, totaldials,
cast((totaldials/cast(strategyvolume as decimal (9,2))*100)as decimal (9,2)) as penetration,
accountscalled,dialerconnects,
cast ((totaldials/cast (dialerconnects as decimal (9,2))*10)as decimal (9,2))as connectspercent,
contacts, cast((contacts/cast(dialerconnects as decimal (9,2))*100) as decimal (9,2))as contactpercent,
cast((dialerconnects/cast(totaldials as decimal(9,2))*10)as decimal (9,2)) as RPCtodial,
promisestopay,
cast ((promisestopay/cast(contacts as decimal (9,2))*100) as decimal (9,2))as ptppercent,
cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))as signedinduration,
strategyvolume/(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as accountsperhour,
dialerconnects /(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as connectsperhour,
promisestopay/(cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as ptpperhour,
contacts/cast ((accountscalled)as decimal (9,2))*100 as contactpercent,
contacts/ (cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as contactsperhour,
totaldials / (cast ((signedinduration/cast (3600 as decimal (9,2))) as decimal (9,2))) as dialsperhour,
answers, abandons, abandons / cast ((answers) as decimal (9,2))*100 as abandonspercent,
allocatedtime, availabletime, wraptime, talktime
from vw_sfrefresh
where report_id = 3
Fetch Next from DMSexportcursor
end
Close DMSexportcursor
Deallocate DMSexportcursor
July 9, 2003 at 3:21 pm
Hmmmm - I may be missing something. Isn't it true that if you FETCH NEXT without using the INTO option that the resulting dataset is just returned to the calling routine. Since the IF statement (If (select sum(strategyvolume) from tbl_report where report_id = 1 ) >'0') works on the table and not on the cursor, it is always true.
Perhaps you mean FETCH ...INTO @wrkDate, @dwnldvol, etc. etc.
Guarddata-
July 10, 2003 at 2:51 am
I've only given the code a quick once over, but it looks like you could do away with the cursor. Cursors are heavy on resources, so it tends to be better to avoid them if possible.
You can try using a sub-query and CASE WHEN ... ELSE ... END for each of the columns that vary in your SELECTs. You will end up with a large SELECT statement, but if programmed correctly should run faster.
As far as your cursor goes Guarddata is right - you are just looping round exactly the same query for the number of matching rows in tbl_report. There are a couple of good examples of Cursors in Books Online, see DECLARE CURSOR under Transact-SQL Reference
July 10, 2003 at 4:12 pm
Phew what a mess!!
I'll not bother to decipher all that, suffice to say it looks way to bloated for its own good and probably the schema is in a bit of a bad way.
But to the cursor... what is it for? You don't use it at all!! All you are doing is executing the containing SQL a number of times, the number of rows that result from the cursor definition query in fact.
Guarddata is write... Fetch next from Dmsexportcursor ... into what???
The whole point of a cursor is that you fetch the results of the cursor query one row at a time into one or more local variables for processing within the cursor.
You don't do this, your cursor is meaningless. I suggest you read up on cursors in Books On-Line.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply