[RESOLVED] Sql server 2000, temp table

[dfuas]

Hi,

I am having a problem to solve this temporary table I am creating.

As you can see below, I am problem in solving [>10secs] and [%>10secs], please see explanation below and your help is very much appreciated.

CREATE TABLE #tmp ( 
   [Date]     varchar(8),
   [Day]     varchar(30),
   Offered     int,
   [Call Answered by IVR]   int,
   [% Answered IVR]   float,
   [Offered Agents]   int,
   [% Answered Agents]   float,
   [Answered by Agents]   int,
   Calls     int,
   --Mailbox    int,
   [> 10 secs]    int,
   [% > 10 secs]    int )

--Offered--

insert into #tmp ([Date],[Day],Offered)
select  convert(varchar,last_updated_at,3),
  Max(datename(dw, last_updated_at)),
  sum(case when campaign_id in ('647') then 1 else 0 end)
FROM          tbcallhistory (nolock)
Where  campaign_id in ('647')
and  call_result  in(9,25,31,48,138,139,141,142,179)
--and  last_updated_at between @startdate and @enddate
and  last_updated_at between '01/08/2007' and '31/08/2007'
group by convert(varchar,last_updated_at,3)
 

--Call Answered by IVR--

insert into #tmp ([Date],[Day],[Call Answered by IVR])
select  convert(varchar,last_updated_at,3),
  Max(datename(dw, last_updated_at)),
  sum(case when campaign_id in ('647') then 1 else 0 end)
FROM          tbcallhistory (nolock)
where  campaign_id in ('647')
and  call_result in (25,142)
--and  last_updated_at between @startdate and @enddate
and  last_updated_at between '01/08/2007' and '31/08/2007'
group by convert(varchar,last_updated_at,3)


--% Answered IVR--

--Calls offered to Agents--

insert into #tmp ([Date],[Day],[Offered Agents])
select  convert(varchar,last_updated_at,3),
  Max(datename(dw, last_updated_at)),
  sum(case when campaign_id in ('647') then 1 else 0 end)
FROM          tbcallhistory (nolock)
Where  campaign_id in ('647')
and  call_result in (9,31,48,138,139,141)
--and   Last_updated_by <> 0
--and  last_updated_at between @startdate and @enddate
and  last_updated_at between '01/08/2007' and '31/08/2007'
group by convert(varchar,last_updated_at,3)

--% Answered Agents--


--Number call Answered by Agents--

insert into #tmp ([Date],[Day],[Answered by Agents])
select  convert(varchar,last_updated_at,3),
  Max(datename(dw, last_updated_at)),
  sum(case when campaign_id in ('647') then 1 else 0 end)
FROM          tbcallhistory (nolock)
where  campaign_id in ('647')
and  Last_updated_by <> 0
and  call_result in (9,31,48,138,139,141)
--and  last_updated_at between @startdate and @enddate
and  last_updated_at between '01/08/2007' and '31/08/2007'
group by convert(varchar,last_updated_at,3)

Calls = (Calls offered to Agents) - (Number call Answered by Agents)

[> 10 secs] = Calls that were abandoned in over 10 secs
what I have is, but I know is not correct:
insert into #tmp ([Date],[Day],[> 10 secs])
select  convert(varchar,last_updated_at,3),
  Max(datename(dw, last_updated_at)),
  --sum(case when campaign_id in ('647') then 1 else 0 end)
  isnull(sum([Offered Agents],0)- isnull(sum([Answered by Agents],0)
FROM          tbcallhistory (nolock)
Where  campaign_id in ('647')
 and  Queue_Duration>10
and  last_updated_at between '01/08/2007' and '31/08/2007'
group by convert(varchar,last_updated_at,3)

--[% > 10 secs]  = percentage of  Calls , ie 
(Calls offered to Agents) - (Number call Answered by Agents) 

select   
  Convert(datetime,[Date],3)                           as [Date], 
  [Day]                                                        as [Day],   
  isnull(sum(Offered),0)                                   as Offered,
  isnull(sum([Call Answered by IVR]),0)               as [absolut IVR],
  isnull(sum([Call Answered by IVR]),0)*1.0 /isnull(sum(Offered),1)  as [% Answered IVR],
  isnull(sum([Offered Agents]),0)                          as [Offered Agents],
  isnull(sum([Offered Agents]),0)*1.0 /isnull(sum(Offered),1)   as [% Answered Agents],
  isnull(sum([Answered by Agents]),0)                   as [Answered by Agents],
  isnull(sum([Offered Agents],0)- isnull(sum([Answered by Agents],0) as Calls,
  --isnull(sum(Mailbox),0)      as Mailbox,
  [> 10 secs]     ?   as [> 10 secs],
  [% > 10 secs] ?   as [% > 10 secs] 

from  #tmp
group by Convert(datetime,[Date],3)  

[techgnome]

Okay... I'll byte... what's the problem?

-tg