February 13, 2015 at 12:18 pm
I am really a SQL newbie and have no idea why I am getting this error. I attemoted to look on some websites and got more confused. They only want to stop the error from displaying. Shouldn't I be looking to correct this? Here is a copy of the code I am running.
use dHEAT_ESEF
set nocount on
go
declare @startdate char(10)
declare @enddate char(10)
/*
ENTER THE DATE RANGE YOU WANT TO REPORT BELOW IN THE FORMAT SHOWN.
*/
select @startdate = '2014-11-01'
select @enddate = '2015-01-31'
INSERT INTO ticket_work
select c.callid,
c.calltype,
a.dateassign,
a.timeassign,
a.dateacknow,
a.timeacknow
from dHEAT_ESEF..calllog c join dHEAT_ESEF..asgnmnt a on c.callid =
a.callid
where a.dateacknow is NOT NULL and
a.timeacknow is NOT NULL
AND a.dateacknow >= @startdate and a.dateacknow <= @enddate
/*
ADD CALLTYPES TO BE EXCLUDED TO THE LIST BELOW. BE SURE THERE THERE ARE PARENTHESES
AROUND THE LIST AND ENCLOSE EACH VALUE IN SINGLE QUOTES. THE CALLTYPE MUST
BE ENTERED JUST AS IT APPEARS IN THE HEAT DATABASE.
*/
AND c.calltype NOT in( 'DESKTOP SW IC', 'DVTT-QLTY CNTR', 'DVTT_T2', 'LM LAN TEAM',
'HEATWEB','OTHER HELP DESK','PCOM','SHIFT CHECKLIST', 'PAIRS', 'AUTO TICKET GEN','VTTS','DB2',
'MADAM', 'IDMS', 'DVTT_VDB', 'QA2 ADMIN', 'TS TECH OTHER', 'CSC LAN TEAM', 'TS RESET$ID/PIN', 'DVTT_QC_NEW/DELETE_USER')
and
a.dateacknow + ' ' + a.timeacknow =
(select min(x.dateacknow + ' ' + x.timeacknow) from dHEAT_ESEF.dbo.asgnmnt x where a.callid = x.callid)
order by c.callid
truncate table ticket_work2
insert into ticket_work2(callid, calltype,dateassign,timeassign,dateacknow,timeacknow)
select
callid,
calltype,
dateassign,
timeassign,
dateacknow,
timeacknow
FROM ticket_work group by callid, calltype, dateassign, timeassign,
dateacknow, timeacknow
update ticket_work2 set dateAssign_date = convert(datetime,dateassign),
timeAssign_hrs = convert(integer,substring(timeassign,1,2)),
timeAssign_min = convert(integer,substring(timeassign,4,2)),
dateAcknow_date = convert(datetime,dateacknow),
timeAcknow_hrs = convert(integer,substring(timeacknow,1,2)),
timeAcknow_min = convert(integer,substring(timeacknow,4,2))
/*
same day calculation has to account for for needing to 'borrow' an hour
*/
update ticket_work2 set hours_wait = timeAcknow_hrs - timeAssign_hrs,
minutes_wait = timeAcknow_min - timeAssign_min where
dateassign = dateacknow and timeAcknow_min >= timeAssign_min
update ticket_work2 set hours_wait = (timeAcknow_hrs - 1) - timeAssign_hrs,
minutes_wait = (timeAcknow_min + 60) - timeAssign_min where
dateassign = dateacknow and timeAcknow_min < timeAssign_min
declare @tAHrs integer
declare @tamin-2 integer
declare @dA_dt datetime
declare @tAcHrs integer
declare @tAcMin integer
declare @dAc_dt datetime
declare @loop integer
declare @ctr integer
declare @hw integer
declare @mw integer
declare @datework datetime
declare @uniqueid integer
declare @X char(1)
DECLARE c1 CURSOR FOR
SELECT dateAssign_date, timeAssign_hrs, timeAssign_min,
dateAcknow_date, timeAcknow_hrs , timeAcknow_min,
hours_wait, minutes_wait, unique_id
from ticket_work2
WHERE hours_wait is NULL and minutes_wait is NULL
order by unique_id
FOR UPDATE
OPEN c1
FETCH NEXT FROM c1
INTO @dA_dt, @tAHrs, @tamin-2, @dAc_dt, @tAcHrs, @tAcMin, @hw, @mw, @uniqueid
WHILE @@FETCH_STATUS = 0
BEGIN
select @loop = datediff(dd,@dA_dt,@dAc_dt)
select @ctr = 0
while @ctr <= @loop
BEGIN
if @ctr = 0
begin
if @tAHrs >= 18
begin
select @hw = 0
select @mw = 0
end
else
begin
select @hw = 17 - @tAHrs
select @mw = 60 - @tamin-2
end
end
if @ctr > 0 AND @ctr < @loop
BEGIN
select @datework = dateadd(day,@ctr,@dA_dt)
select @X = 'N'
select @X = 'Y' from holidays where convert(char(10),@datework,101)
= convert(char(10),holiday_dt,101)
if @X = 'Y'
begin
select @ctr = @ctr + 1
continue
end
if datepart(dw,@datework) = 1 or datepart(dw,@datework) = 7
begin
select @ctr = @ctr + 1
continue
end
select @hw = @hw + 12
END
if @ctr = @loop
begin
select @hw = @hw + @tAcHrs - 6
select @mw = @mw + @tAcMin
end
select @ctr = @ctr + 1
END
UPDATE ticket_work2 set hours_wait = @hw, minutes_wait = @mw WHERE CURRENT OF c1
FETCH NEXT FROM c1
INTO @dA_dt, @tAHrs, @tamin-2, @dAc_dt, @tAcHrs, @tAcMin, @hw, @mw, @uniqueid
END
CLOSE c1
DEALLOCATE c1
update ticket_work2 set hours_wait = hours_wait + floor(minutes_wait/60),
minutes_wait = minutes_wait - (floor(minutes_wait/60) * 60)
where minutes_wait >= 60
INSERT into ticket_work3
select calltype, sum(hours_wait) hours_wait, sum(minutes_wait) minutes_wait, count(unique_id) call_count, convert(integer,0) work1,
convert(integer,0) work2, convert(varchar(20),' ') avg_time
from ticket_work2
/* eliminate tickets where acknow time < assign time, theoretically impossible */
where hours_wait is NOT NULL and minutes_wait is NOT NULL and hours_wait >= 0 and
minutes_wait >= 0
group by calltype
update ticket_work3 set work1 = round((hours_wait * 60 + minutes_wait) / call_count,0)
update ticket_work3 set work2 = work1 % 60
update ticket_work3 set work1 = (work1 - work2) / 60
update ticket_work3 set avg_time = rtrim(convert(char(10),work1)) + ':' + convert(char(2),work2)
/*
Modified the following select statement to display the summation of hours and minutes
for calculating average ticket acknowledgement
*/
select 'AVG Call Time By Call Category: ' + @startdate + ' through ' + @enddate 'Title',
count(calltype) '# of call types',
sum(call_count) 'Total Tickets Acknowleged',
sum(hours_wait) 'Sum of hrs',
sum(minutes_wait) 'Sum of min',
(sum(hours_wait)*60+sum(minutes_wait)) 'Total Minutes'
from ticket_work3
/* List of Average Acknowledge times per team */
select calltype 'Call Type', call_count 'No. of Calls', avg_time 'Avg Ack Time (HH:MM)'
from ticket_work3 order by calltype
February 13, 2015 at 12:46 pm
Hi, welcome to the forums.
You posted almost 300 lines of code (after formatting) which is considered rude for a simple problem. You should identify where the error is and without dynamic sql or stored procedures called in the code is pretty easy, you just need to double-click on the error.
My guess (as is the only variable division I found) is that your problem is the following update.
UPDATE ticket_work3
SET work1 = round((hours_wait * 60 + minutes_wait) / call_count, 0)
In some rows, your call_count value is zero and that's giving you an error (nothing can be divided by zero). The question is, what do you want to get? A NULL value? A zero? a one?
One solution that I use, is to use NULLIF function.
UPDATE ticket_work3
SET work1 = round((hours_wait * 60 + minutes_wait) / NULLIF( call_count, 0), 0)
This will return a null value and you'll end with null values on work1, work2 and avg_time.
Let me give it some more time to analyze the code to give you an even better alternative.
EDIT: Can you post DDL and sample data to work against it? Read the article linked in my signature to learn how to do it.
February 13, 2015 at 2:03 pm
Further on Luis's post, simply look in the code for the pattern division by a column or a variable such as X / [column_name]
😎
February 13, 2015 at 2:54 pm
OK. I will post and not ignore that huge white elephant over there in the corner.
Your code has some very major performance problems. You have a cursor. Then inside your cursor you have a while loop. Looping in any DBMS is horribly inefficient. This entire nested looping structure could be rewritten as a single update statement. Or is this whole process really just a couple of select statements from the original query?
Then we have the issue of 3 tables to hold the data of one. From what I see there is no need at all for all these copies of ticket_work to hold the intermediate results.
Last but not least, you have @startdate and @enddate declared as char(10). These are dates, you should use the date datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply