multiple calculations on same dataset

[lpa]

I am trying to create summary totals on a dataset.

Data is like:

seq_num ring_start_time abandoned call_pu_time
775544 2/17/2005 10:30:02 F 2/17/2005 10:30:15
775545 2/17/2005 10:30:22 F 2/17/2005 10:50:35
775546 2/17/2005 11:30:42 T
775547 2/17/2005 11:31:02 F 2/17/2005 11:31:15

If the abandoned field is T, the call_pu_time field is null.

They are looking for is the output to look like:

time_period # of calls %within Service Levl %abandoned
10:30 - 11:00 2 50% 0
11:30 - 12:00 2 100% 50

%within service level is based on 20 seconds (pick up the call within 20 sec and you are ok).

I am fairly basic with SQL, so I am trying to find a way to do this with a select statement and DTS. My goal is to move the data from Oracle and provide to another appl in a flat file delimited format. The data source is Oracle, but I can create temporary tables in a SQL environment if I need to.

Right now, all I have is the first two fields - half hour and the count of the records to call the number of calls (cudos to vongrunt and dountman for posts explaining the half hour calc thread183-923209).

When I try to do the calculation for %within service level, I am trying to do multiple aggregate functions and cannot.

Any suggestions for how to approach this (ie - learn to write a stored procedure????)

 

[donutman]

You have to add a where clause that filters out those calls that are not answered within 20 seconds. Do that with:

[Blue]WHERE[/Blue] 20[Gray]<[/Gray][Fuchsia]DateDiff[/Fuchsia][Gray]([/Gray]ss[Gray],[/Gray]0[Gray],[/Gray]EndTime[Gray]-[/Gray]StartTime[Gray])[/Gray]

If you need more help then post your current query for getting the totals.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[lpa]

Thank you donutman...of course the users have modified the request. Oh well - I appreciate the response.