how to query 90 percent of data in access sql

[lokeshsu]

Hi all,

i have a database which is getting updated 6000 records every day which calculates me the Turn around time.

the data is given below

Priorits SDate ODate Diff Emp Supervisor
1-ASAP 01-Nov-11 01-Nov-11 0.00 A,b team A
1-ASAP 02-Nov-11 01-Nov-11 0.00 b,a Team B
1-ASAP 04-Nov-11 01-Nov-11 0.00 c,d Team D
1-ASAP 03-Nov-11 01-Nov-11 0.00 d,c Team C
1-ASAP 01-Nov-11 01-Nov-11 0.00 A,b team A
2-High 02-Nov-11 01-Nov-11 2.00 b,a Team B
2-High 04-Nov-11 01-Nov-11 4.00 c,d Team D
2-High 03-Nov-11 01-Nov-11 8.00 d,c Team C
2-High 01-Nov-11 01-Nov-11 10.67 A,b team A
2-High 02-Nov-11 01-Nov-11 13.67 b,a Team B
2-High 04-Nov-11 01-Nov-11 16.67 c,d Team D
2-High 03-Nov-11 01-Nov-11 19.67 d,c Team C
2-High 01-Nov-11 01-Nov-11 22.67 A,b team A
3-Medium 02-Nov-11 01-Nov-11 25.67 b,a Team B
3-Medium 04-Nov-11 01-Nov-11 28.67 c,d Team D
3-Medium 03-Nov-11 01-Nov-11 31.67 d,c Team C
3-Medium 01-Nov-11 01-Nov-11 34.67 A,b team A
3-Medium 02-Nov-11 01-Nov-11 37.67 b,a Team B
4-Low 04-Nov-11 01-Nov-11 40.67 c,d Team D
4-Low 03-Nov-11 01-Nov-11 43.67 d,c Team C
4-Low 01-Nov-11 01-Nov-11 46.67 A,b team A
4-Low 02-Nov-11 01-Nov-11 49.67 b,a Team B
4-Low 04-Nov-11 01-Nov-11 52.67 c,d Team D
4-Low 03-Nov-11 01-Nov-11 55.67 d,c Team C
4-Low 01-Nov-11 01-Nov-11 58.67 A,b team A
4-Low 02-Nov-11 01-Nov-11 61.67 b,a Team B


Step 1.

Sort the diff in ascending order

step 2.

take the count of all records

Step 3.
calculate the average diff time of the 90% of the records




Question
1. How do i count 90% of records displayed by dates
2. i need the top 90 % which should round up (>0.5) or round down (<0.5)

can anybody please help me in building this sql query which would save my time and my life. thanks

 

[xenou]

Not sure but you can try:

SELECT Round(AVG(tSub.Diff),0) AS AverageOfDiff FROM
 (SELECT TOP 90% TABLE1.Diff 
  FROM TABLE1 
  ORDER BY TABLE1.Diff) AS tSub;

This isn't an exact solution as TOP returns ties so you may get more than 90% - if that not acceptable you'll have to go to some further lengths (this post may help: http://rogersaccessblog.blogspot.com/2009/12/top-query-problem-ties.html)

Note that in practice I more often write queries that query other queries in Access, than true subqueries as this sometimes gives better performance in Access:

Query1:

SELECT TOP 90% TABLE1.Diff 
  FROM TABLE1 
  ORDER BY TABLE1.Diff;

Query2:

SELECT Avg(Query1.Diff) FROM Query1;