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.