Awkward Query Idea / Help!? Percentiles.

S

stevensjn

Hi everyone,

I've been tasked with creating a report (manually, not a report in
Access per se) that details EMS response times. Our service maintains
EMS in a regional municipality that contains many "lower tier"
municipalities. The query needs to be grouped by lower tier and month.

I'm trying to create the report's data set in one query (with a
subquery or some such thing).

The main portion of the query returns average response times based on
DateDiff (in seconds) between a start and stop time that uses the
aggregate AVG grouped by month and lower tier.

I need to add a 90th percentile value which I can't seem to do other
than by running about 200 queries, one for each lower tier, for each
month. As you can imagine that would be a major time sink that I would
like to avoid doing, ever.

Below are the queries. As the tables are quite large I wont post them
just yet, as I'm looking for a concept more than anything else.
LowerTierMaster can be thought of as a lookup of IDs and municipality
names (LowerTier is INT, Municipality is TEXT). Hopefully this is
enough info.

SELECT
MAX(MonthName(Datepart("m", CallDispatched.StartTime))) AS
TheMonth,
MAX(Datepart("m", CallDispatched.StartTime)) AS MonthNum,
MAX(LowerTierMaster.Municipality) AS TheLowerTier,
Count(*) AS NumCalls,

AVG(DateDiff('s',[CallDispatched.StartTime],[CallDispatched.EndTime]))
AS AveResponseTotalSeconds,

AVG(DateDiff('s',CallDispatched.ArriveHospital,CallDispatched.LeaveHospital))
AS AveOffLoadSeconds

FROM
CallDispatched,
LowerTierMaster

WHERE
(CallDispatched.PickupLowerTier = LowerTierMaster.LowerTier) AND
(CallDispatched.StartTime > #09/30/2005 23:59:59# AND
CallDispatched.StartTime < #10/01/2006 00:00:00#) AND
DateDiff("s", CallDispatched.StartTime, CallDispatched.EndTime)

GROUP BY
Datepart("m", CallDispatched.StartTime),
CallDispatched.PickupLowerTier

ORDER BY
2, 3, 4;


I have the following code for running a single 90th percentile on one
lower tier, for one month but I have to build a table of the right data
to run the query on for every month and every lower tier. I would like
to use the results from either the above query ... or a query similar
to the above to make a single data set that I could run a query against
to get the 90th percentiles for all month and lower tiers in one shot.

SELECT
DISTINCT TOP 1 TotalOffloadSeconds

FROM
[SELECT
TOP 90 PERCENT TotalOffloadSeconds,
LowerTier
FROM
tblCallsTemp
WHERE
LowerTier=3731 AND
MonthNum=1
ORDER BY
TotalOffloadSeconds DESC]. AS [90th Percentile]

God help me. :)

Thanks.
 
S

Smartin

Hi everyone,

I've been tasked with creating a report (manually, not a report in
Access per se) that details EMS response times. Our service maintains
EMS in a regional municipality that contains many "lower tier"
municipalities. The query needs to be grouped by lower tier and month.

I'm trying to create the report's data set in one query (with a
subquery or some such thing).

The main portion of the query returns average response times based on
DateDiff (in seconds) between a start and stop time that uses the
aggregate AVG grouped by month and lower tier.

I need to add a 90th percentile value which I can't seem to do other
than by running about 200 queries, one for each lower tier, for each
month. As you can imagine that would be a major time sink that I would
like to avoid doing, ever.

Below are the queries. As the tables are quite large I wont post them
just yet, as I'm looking for a concept more than anything else.
LowerTierMaster can be thought of as a lookup of IDs and municipality
names (LowerTier is INT, Municipality is TEXT). Hopefully this is
enough info.

SELECT
MAX(MonthName(Datepart("m", CallDispatched.StartTime))) AS
TheMonth,
MAX(Datepart("m", CallDispatched.StartTime)) AS MonthNum,
MAX(LowerTierMaster.Municipality) AS TheLowerTier,
Count(*) AS NumCalls,

AVG(DateDiff('s',[CallDispatched.StartTime],[CallDispatched.EndTime]))
AS AveResponseTotalSeconds,

AVG(DateDiff('s',CallDispatched.ArriveHospital,CallDispatched.LeaveHospital))
AS AveOffLoadSeconds

FROM
CallDispatched,
LowerTierMaster

WHERE
(CallDispatched.PickupLowerTier = LowerTierMaster.LowerTier) AND
(CallDispatched.StartTime > #09/30/2005 23:59:59# AND
CallDispatched.StartTime < #10/01/2006 00:00:00#) AND
DateDiff("s", CallDispatched.StartTime, CallDispatched.EndTime)

GROUP BY
Datepart("m", CallDispatched.StartTime),
CallDispatched.PickupLowerTier

ORDER BY
2, 3, 4;


I have the following code for running a single 90th percentile on one
lower tier, for one month but I have to build a table of the right data
to run the query on for every month and every lower tier. I would like
to use the results from either the above query ... or a query similar
to the above to make a single data set that I could run a query against
to get the 90th percentiles for all month and lower tiers in one shot.

SELECT
DISTINCT TOP 1 TotalOffloadSeconds

FROM
[SELECT
TOP 90 PERCENT TotalOffloadSeconds,
LowerTier
FROM
tblCallsTemp
WHERE
LowerTier=3731 AND
MonthNum=1
ORDER BY
TotalOffloadSeconds DESC]. AS [90th Percentile]

God help me. :)

Thanks.
Click to expand...

Hello,

I think I'm in a similar boat. I have a huge table of transactions from
which I need to determine a few percentile values across several subsets
of the data (e.g., by month, transaction type, etc.)

I do this by creating queries that extract the subset parameters from
the table and build a query that finds the percentiles on the fly. As I
loop through the parameters I run each query, and store the results in a
separate table.

Much of this in done in VBA using recordsets.

Sorry for the generalized answer. Hope it helps or at least gives you
some ideas.
 
S

stevensjn

[email protected] said:
Hi everyone,
Click to expand...
I've been tasked with creating a report (manually, not a report in
Access per se) that details EMS response times. Our service maintains
EMS in a regional municipality that contains many "lower tier"
municipalities. The query needs to be grouped by lower tier and month.
Click to expand...
I'm trying to create the report's data set in one query (with a
subquery or some such thing).
Click to expand...
The main portion of the query returns average response times based on
DateDiff (in seconds) between a start and stop time that uses the
aggregate AVG grouped by month and lower tier.
Click to expand...
I need to add a 90th percentile value which I can't seem to do other
than by running about 200 queries, one for each lower tier, for each
month. As you can imagine that would be a major time sink that I would
like to avoid doing, ever.
Click to expand...
Below are the queries. As the tables are quite large I wont post them
just yet, as I'm looking for a concept more than anything else.
LowerTierMaster can be thought of as a lookup of IDs and municipality
names (LowerTier is INT, Municipality is TEXT). Hopefully this is
enough info.
Click to expand...
SELECT
MAX(MonthName(Datepart("m", CallDispatched.StartTime))) AS
TheMonth,
MAX(Datepart("m", CallDispatched.StartTime)) AS MonthNum,
MAX(LowerTierMaster.Municipality) AS TheLowerTier,
Count(*) AS NumCalls,
Click to expand...
AVG(DateDiff('s',[CallDispatched.StartTime],[CallDispatched.EndTime]))
AS AveResponseTotalSeconds,
Click to expand...
AVG(DateDiff('s',CallDispatched.ArriveHospital,CallDispatched.LeaveHospital))
AS AveOffLoadSeconds

WHERE
(CallDispatched.PickupLowerTier = LowerTierMaster.LowerTier) AND
(CallDispatched.StartTime > #09/30/2005 23:59:59# AND
CallDispatched.StartTime < #10/01/2006 00:00:00#) AND
DateDiff("s", CallDispatched.StartTime, CallDispatched.EndTime)
Click to expand...
GROUP BY
Datepart("m", CallDispatched.StartTime),
CallDispatched.PickupLowerTier
Click to expand...
ORDER BY
2, 3, 4;
Click to expand...
I have the following code for running a single 90th percentile on one
lower tier, for one month but I have to build a table of the right data
to run the query on for every month and every lower tier. I would like
to use the results from either the above query ... or a query similar
to the above to make a single data set that I could run a query against
to get the 90th percentiles for all month and lower tiers in one shot.
Click to expand...
SELECT
DISTINCT TOP 1 TotalOffloadSeconds
Click to expand...
FROM
[SELECT
TOP 90 PERCENT TotalOffloadSeconds,
LowerTier
FROM
tblCallsTemp
WHERE
LowerTier=3731 AND
MonthNum=1
ORDER BY
TotalOffloadSeconds DESC]. AS [90th Percentile]
Click to expand...
God help me. :)
Click to expand...
Thanks.
Click to expand...

Hello,

I think I'm in a similar boat. I have a huge table of transactions from
which I need to determine a few percentile values across several subsets
of the data (e.g., by month, transaction type, etc.)

I do this by creating queries that extract the subset parameters from
the table and build a query that finds the percentiles on the fly. As I
loop through the parameters I run each query, and store the results in a
separate table.

Much of this in done in VBA using recordsets.

Sorry for the generalized answer. Hope it helps or at least gives you
some ideas.
Click to expand...

Thanks, that's the sort of reply I was hoping to get. I ended up doing
the same, but it does seem a little bulky. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help w/query pls 1
Please help with this query 1
Crosstab Query help 2
Sort by Month in a query 3
HELP WITH QUERY BY DATE 2
Exported query Runs forever, creates huge spreadsheet with no data. 2
Help with query 6
Crosstab Query Help 1

Top