Query to show telephone allowances (1 Viewer)

[ot070707]

I'm analysing phone calls and costs for my employer.
I have a table of around 8 million call records giving amongst other things
- DistanceBand (eg Local, National, International)
- Time Band (eg Daytime, Evening, Weekend)
- Duration of call in seconds
- Cost of call
As you would expect, we pay a fixed charge for the first X seconds then a variable charge thereafter.
I'm trying to write a query which gives me a summary of
- DistanceBand
- TimeBand
- Minimum Cost
- Maximum Time allowed for that minimum cost

I can do the first three elements of the query relatively easily using the Min function, its getting to the maximum time allowed for the minimum cost which is proving a bit more difficult.
I think it can be done by creating a seperate query of minimum costs and joining it to my table of calls, but that looks a bit messy and I was wondering if it could all be done in just one query. I've tried using the 'where' and 'expression' options in the summing query but I'm missing a trick somewhere.
Any suggestions?
Many thanks.

 

[jzwp22]

I think you may need a subquery. I'm not sure if it will work within the query that you are currently using to get the Min(Cost) or you whether you have to create a second query that brings in everything from the first query (Q1) and has the subquery

SELECT Q1.TimeBand, Q1.DistanceBand, Q1.MinOfCost, (Select max(q2.duration) from table1 as Q2 where Q2.DistanceBand=Q1.distanceband and Q2.timeband=q1.timeband and Q2.cost=q1.MinOfCost) as Maxtime
FROM Q1;

Wouldn't the records with the lowest cost/second (in each time/distance band) get you to the same result?

 

[ot070707]

Thanks for that.
Both suggestions look like they'll give me what I need.
I'll give it a try at work tomorrow and let you know how I get on.
Many thanks.

 

[jzwp22]

You're welcome. Please let us know if you have additional questions once you have a chance to try it out.