Query to calculate TAT

[navin]

Hi,

I am trying to write a turn around time (TAT) query for one of my
tables. Users save the number of orders they placed in a table by Type
of PO (Standard and HOT) orders. when they save the details,
application calculates and saves the TAT value for each PO. so if TAT
for a PO is >2 days then it has missed the TAT.

I need to calculate how many Standard or Hot POs have missed TAT in
percentage.

Table:

PO# PO Type TAT (In Days)
1234 Standard 1
53453 Standard 1
45454 Standard 3
8787 Hot 1
6676 Hot 4
5655 Hot 5

formula used: count of POs which have missed TAT / total count of POs

In the above table out 3 standard PO, 1 PO has missed the TAT. so it
should calculate 1/3 or (33%)

Output:

Standard: 33%
Hot: 66%

I dont know how to get this.

Thank you in advance for the help.

Navin

 

[Dirk Goldgar]

in message

Hi,

I am trying to write a turn around time (TAT) query for one of my
tables. Users save the number of orders they placed in a table by Type
of PO (Standard and HOT) orders. when they save the details,
application calculates and saves the TAT value for each PO. so if TAT
for a PO is >2 days then it has missed the TAT.

I need to calculate how many Standard or Hot POs have missed TAT in
percentage.

Table:

PO# PO Type TAT (In Days)
1234 Standard 1
53453 Standard 1
45454 Standard 3
8787 Hot 1
6676 Hot 4
5655 Hot 5

formula used: count of POs which have missed TAT / total count of POs

In the above table out 3 standard PO, 1 PO has missed the TAT. so it
should calculate 1/3 or (33%)

Output:

Standard: 33%
Hot: 66%

I dont know how to get this.

Thank you in advance for the help.

Try a query with SQL along these lines:

SELECT
[PO Type],
Sum(IIf([TAT]>2,1,0))/Count(*) AS OverTAT
FROM tblPurchaseOrders
GROUP BY [PO Type];

That will give you the calculated value as a decimal fraction (e.g.,
0.33333333), but you can then format it as a percent.