[RESOLVED] Sql server 2000, temp table

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.

HTML Code:

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)