Calculating Percentages

  • Thread starter Thread starter hotplate
  • Start date Start date
H

hotplate

Suppose I had records that had a starting date and a closing date.

How would I find the percentage of records that closed between 1 and
10 days, 11 - 20 days, 21 - 30 days, and 31 + days?

I think I can do this with about 5 queries, but is there an easier way?
 
You should be able to do this all in one query.

SELECT Count(IIF(DateDiff("d",[StartingDate],[Closing Date]<=10,1,Null)) as
1To10Days
, Count(IIF(DateDiff("d",[StartingDate],[closing Date] Between 11 and
20,1,Null)) as 11To20Days
, Count(*) as TotalRecords
, Count(IIF(DateDiff("d",[StartingDate],[closing Date]<=10,1,Null))/Count(*)
as Percent1To10Days
FROM [Some Table]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you.


You should be able to do this all in one query.

SELECT Count(IIF(DateDiff("d",[StartingDate],[Closing Date]<=10,1,Null)) as
1To10Days
, Count(IIF(DateDiff("d",[StartingDate],[closing Date] Between 11 and
20,1,Null)) as 11To20Days
, Count(*) as TotalRecords
,  Count(IIF(DateDiff("d",[StartingDate],[closing Date]<=10,1,Null))/Count(*)
as Percent1To10Days
FROM [Some Table]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Suppose I had records that had a starting date and a closing date.
Click to expand...
How would I find the percentage of records that closed between 1 and
10 days, 11 - 20 days, 21 - 30 days, and 31 + days?
Click to expand...
I think I can do this with about 5 queries, but is there an easier way?
Click to expand...
Click to expand...
 
Back
Top