Calculate 90th Percentile using top 10 percent

[Dale]

Hello
I have two groups of which I want to calculate the 90P for two different
processes (A and B) by week. I am trying to show the last value of
the top 10 % sorted in desc order for each group and process/week. I've
tried doing this through a subquery with not much success. Not to mention
its slower than molasses!

Group 90P A 90P B
3100 34 106
3130 28 150

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
GROUP BY T1.WGroup, T1.WeekID, T1.ProcessA, T1.REQUEST_TIME, T1.REP_PRIORITY
HAVING (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData as
T2 where T2.WGroup like t1.[WGroup] and t2.weekid like t1.[weekid]and
t2.rep_priority like t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND ((T1.REQUEST_TIME)
Between #12/30/1899 6:0:0# And #12/30/1899 23:59:0#) AND ((T1.REP_PRIORITY)
Like "rt" Or (T1.REP_PRIORITY) Like "rr"));

Any suggestions are welcome..tia

 

[Ken Snell \(MVP\)]

Try this:

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
WHERE (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData as
T2 where T2.WGroup = t1.[WGroup] and t2.weekid = t1.[weekid] and
t2.rep_priority = t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND ((T1.REQUEST_TIME)
Between #6:0:0# And #23:59:0#) AND ((T1.REP_PRIORITY)
= "rt" Or (T1.REP_PRIORITY) = "rr"))

 

[Dale]

Thanks Kevin
Still returns all the items in the top 10 percent, where I only want one row
returned because of the size of the recordset this is about 500 records.
I have a function I normally use but with 10 plus queries each with about 3
different processes I have to set the criteria for each instance...depending
on
the complexity of the underlying query it gets quite cumbersome. I was
looking for a faster method.

Looks like I'm stuck with what I have...which isn't a bad thing!

Try this:

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
WHERE (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData
as
T2 where T2.WGroup = t1.[WGroup] and t2.weekid = t1.[weekid] and
t2.rep_priority = t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND
((T1.REQUEST_TIME)
Between #6:0:0# And #23:59:0#) AND ((T1.REP_PRIORITY)
= "rt" Or (T1.REP_PRIORITY) = "rr"))

--

Ken Snell
<MS ACCESS MVP>

Hello
I have two groups of which I want to calculate the 90P for two different
processes (A and B) by week. I am trying to show the last value of
the top 10 % sorted in desc order for each group and process/week. I've
tried doing this through a subquery with not much success. Not to mention
its slower than molasses!

Group 90P A 90P B
3100 34 106
3130 28 150

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
GROUP BY T1.WGroup, T1.WeekID, T1.ProcessA, T1.REQUEST_TIME,
T1.REP_PRIORITY
HAVING (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData
as T2 where T2.WGroup like t1.[WGroup] and t2.weekid like t1.[weekid]and
t2.rep_priority like t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND
((T1.REQUEST_TIME) Between #12/30/1899 6:0:0# And #12/30/1899 23:59:0#)
AND ((T1.REP_PRIORITY) Like "rt" Or (T1.REP_PRIORITY) Like "rr"));

Any suggestions are welcome..tia

 

[Ken Snell \(MVP\)]

You want just one record? Perhaps this:

SELECT TOP 1 T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
WHERE (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData as
T2 where T2.WGroup = t1.[WGroup] and t2.weekid = t1.[weekid] and
t2.rep_priority = t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND ((T1.REQUEST_TIME)
Between #6:0:0# And #23:59:0#) AND ((T1.REP_PRIORITY)
= "rt" Or (T1.REP_PRIORITY) = "rr"))
ORDER BY T1.WGroup, T1.WeekID, T1.ProcessA

If this isn't the answer, then I obviously am not understanding correctly
what you want to do.....
--

Ken Snell
<MS ACCESS MVP>

Thanks Kevin
Still returns all the items in the top 10 percent, where I only want one
row returned because of the size of the recordset this is about 500
records.
I have a function I normally use but with 10 plus queries each with about
3 different processes I have to set the criteria for each
instance...depending on
the complexity of the underlying query it gets quite cumbersome. I was
looking for a faster method.

Looks like I'm stuck with what I have...which isn't a bad thing!

Try this:

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
WHERE (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from tblData
as
T2 where T2.WGroup = t1.[WGroup] and t2.weekid = t1.[weekid] and
t2.rep_priority = t1.[rep_priority] and t2.request_time between
#06:00:00# and #23:00:00# order by t2.ProcessA desc)) AND
((T1.REQUEST_TIME)
Between #6:0:0# And #23:59:0#) AND ((T1.REP_PRIORITY)
= "rt" Or (T1.REP_PRIORITY) = "rr"))

--

Ken Snell
<MS ACCESS MVP>

Hello
I have two groups of which I want to calculate the 90P for two different
processes (A and B) by week. I am trying to show the last value of
the top 10 % sorted in desc order for each group and process/week. I've
tried doing this through a subquery with not much success. Not to
mention its slower than molasses!

Group 90P A 90P B
3100 34 106
3130 28 150

SELECT T1.WGroup, T1.WeekID, T1.ProcessA
FROM tblData AS T1
GROUP BY T1.WGroup, T1.WeekID, T1.ProcessA, T1.REQUEST_TIME,
T1.REP_PRIORITY
HAVING (((T1.ProcessA) In (Select top 10 percent t2.ProcessA from
tblData as T2 where T2.WGroup like t1.[WGroup] and t2.weekid like
t1.[weekid]and t2.rep_priority like t1.[rep_priority] and
t2.request_time between #06:00:00# and #23:00:00# order by t2.ProcessA
desc)) AND ((T1.REQUEST_TIME) Between #12/30/1899 6:0:0# And #12/30/1899
23:59:0#) AND ((T1.REP_PRIORITY) Like "rt" Or (T1.REP_PRIORITY) Like
"rr"));

Any suggestions are welcome..tia