Percent Utilization Based on a Date Range

G

Guest

I have tried every formula and method I can think of and decide to turn it
over to the experts. I’m trying to get a percent utilization based on a date
range (sent to the query from a form) using Input1 for beginning date and
Input2 for the end date. Below is an example of the date.
Lets say:
Input1 = 1/1/2002
Input2 = 12/31/2004



InService StartRentalDate StopRentalDate DateSold
1/1/1998 1/1/1998 2/19/2003 2/15/2006
1/1/1998 2/19/2003
1/1/1998 4/14/2002 4/15/2002
1/1/1998 2/20/2003 2/16/2004 2/15/2006
2/20/2003 2/16/2004
8/1/1999 9/18/2001 2/15/2002
8/1/1999 9/18/2001 2/15/2002
6/1/1997 10/10/2000 2/5/2002
 
G

Guest

Try this --
SELECT JRyhmes.InService, JRyhmes.StartRentalDate, JRyhmes.StopRentalDate,
JRyhmes.DateSold, DateDiff("d",[StartRentalDate],[StopRentalDate]) AS [Rental
Days], DateDiff("d",[InService],IIf([DateSold] Is
Null,[StopRentalDate],[DateSold])) AS [Days On-hand],
(DateDiff("d",[StartRentalDate],[StopRentalDate]))/(DateDiff("d",[InService],IIf([DateSold]
Is Null,[StopRentalDate],[DateSold])))*100 AS [Percent Utilization]
FROM JRyhmes;
 
G

Guest

Thanks Karl for the help. Sorry to get back so late. Some of this I had but I
did it a longer way. What I can’t figure out if the added burden of having
the Input date range (Input1 and Input2). The conditions for the formula are
getting way too complicated for my understanding of queries. Too many if’s,
and’s and or’s to deal with I guess. I’m having to back out days if, let’s
say input1 is greater then the start rental date or less then the date in
service. Same for input2 and the stop rental date and the date sold.

KARL DEWEY said:
Try this --
SELECT JRyhmes.InService, JRyhmes.StartRentalDate, JRyhmes.StopRentalDate,
JRyhmes.DateSold, DateDiff("d",[StartRentalDate],[StopRentalDate]) AS [Rental
Days], DateDiff("d",[InService],IIf([DateSold] Is
Null,[StopRentalDate],[DateSold])) AS [Days On-hand],
(DateDiff("d",[StartRentalDate],[StopRentalDate]))/(DateDiff("d",[InService],IIf([DateSold]
Is Null,[StopRentalDate],[DateSold])))*100 AS [Percent Utilization]
FROM JRyhmes;


J Rhymes said:
I have tried every formula and method I can think of and decide to turn it
over to the experts. I’m trying to get a percent utilization based on a date
range (sent to the query from a form) using Input1 for beginning date and
Input2 for the end date. Below is an example of the date.
Lets say:
Input1 = 1/1/2002
Input2 = 12/31/2004



InService StartRentalDate StopRentalDate DateSold
1/1/1998 1/1/1998 2/19/2003 2/15/2006
1/1/1998 2/19/2003
1/1/1998 4/14/2002 4/15/2002
1/1/1998 2/20/2003 2/16/2004 2/15/2006
2/20/2003 2/16/2004
8/1/1999 9/18/2001 2/15/2002
8/1/1999 9/18/2001 2/15/2002
6/1/1997 10/10/2000 2/5/2002
Click to expand...
Click to expand...
 
G

Guest

Try this --
SELECT
(DateDiff("d",IIf([Input1]>[StartRentalDate],[Input1],[StartRentalDate]),IIf([Input2]<[StopRentalDate],[Input2],[StopRentalDate])))/(DateDiff("d",[Input1],[Input2]))*100 AS [Percent Utilization]
FROM JRyhmes;



J Rhymes said:
Thanks Karl for the help. Sorry to get back so late. Some of this I had but I
did it a longer way. What I can’t figure out if the added burden of having
the Input date range (Input1 and Input2). The conditions for the formula are
getting way too complicated for my understanding of queries. Too many if’s,
and’s and or’s to deal with I guess. I’m having to back out days if, let’s
say input1 is greater then the start rental date or less then the date in
service. Same for input2 and the stop rental date and the date sold.

KARL DEWEY said:
Try this --
SELECT JRyhmes.InService, JRyhmes.StartRentalDate, JRyhmes.StopRentalDate,
JRyhmes.DateSold, DateDiff("d",[StartRentalDate],[StopRentalDate]) AS [Rental
Days], DateDiff("d",[InService],IIf([DateSold] Is
Null,[StopRentalDate],[DateSold])) AS [Days On-hand],
(DateDiff("d",[StartRentalDate],[StopRentalDate]))/(DateDiff("d",[InService],IIf([DateSold]
Is Null,[StopRentalDate],[DateSold])))*100 AS [Percent Utilization]
FROM JRyhmes;


J Rhymes said:
I have tried every formula and method I can think of and decide to turn it
over to the experts. I’m trying to get a percent utilization based on a date
range (sent to the query from a form) using Input1 for beginning date and
Input2 for the end date. Below is an example of the date.
Lets say:
Input1 = 1/1/2002
Input2 = 12/31/2004



InService StartRentalDate StopRentalDate DateSold
1/1/1998 1/1/1998 2/19/2003 2/15/2006
1/1/1998 2/19/2003
1/1/1998 4/14/2002 4/15/2002
1/1/1998 2/20/2003 2/16/2004 2/15/2006
2/20/2003 2/16/2004
8/1/1999 9/18/2001 2/15/2002
8/1/1999 9/18/2001 2/15/2002
6/1/1997 10/10/2000 2/5/2002
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I think that gets me much closer, but running some manual calculations on it
and looking at the formula, I don't think it's figuring on the possible days.
Input1 and 2 may not be the amount of days to figure a percentage on if
InService day or the Date Sold end up in-between that range (if that makes
any since). I have to calculate on those also.
 
G

Guest

Now that I look at it, I probably need to do it in 3 different formulas. One
to calculate possible days or range it could have been rented, another to
calculate the actual days rented and then calculate between the 2 to get the
percent utilization.

J Rhymes said:
I think that gets me much closer, but running some manual calculations on it
and looking at the formula, I don't think it's figuring on the possible days.
Input1 and 2 may not be the amount of days to figure a percentage on if
InService day or the Date Sold end up in-between that range (if that makes
any since). I have to calculate on those also.

KARL DEWEY said:
Try this --
SELECT
(DateDiff("d",IIf([Input1]>[StartRentalDate],[Input1],[StartRentalDate]),IIf([Input2]<[StopRentalDate],[Input2],[StopRentalDate])))/(DateDiff("d",[Input1],[Input2]))*100 AS [Percent Utilization]
FROM JRyhmes;
Click to expand...
Click to expand...
 
G

Guest

That's Karl for the help. I think I finally got it. This is what I came up
with:

DDIS: IIf([Date in Service] Between [Forms]![Rental_Date]![Text0] And
[Forms]![Rental_Date]![Text1],DateDiff("d",[Date in Service],IIf([Date Sold]
Is Null Or [Date
Sold]>[Forms]![Rental_Date]![Text1],[Forms]![Rental_Date]![Text1],[Date
Sold]),[Forms]![Rental_Date]![Text1]),DateDiff("d",[Forms]![Rental_Date]![Text0],[Forms]![Rental_Date]![Text1]))

KARL DEWEY said:
Try this --
SELECT
(DateDiff("d",IIf([Input1]>[StartRentalDate],[Input1],[StartRentalDate]),IIf([Input2]<[StopRentalDate],[Input2],[StopRentalDate])))/(DateDiff("d",[Input1],[Input2]))*100 AS [Percent Utilization]
FROM JRyhmes;



J Rhymes said:
Thanks Karl for the help. Sorry to get back so late. Some of this I had but I
did it a longer way. What I can’t figure out if the added burden of having
the Input date range (Input1 and Input2). The conditions for the formula are
getting way too complicated for my understanding of queries. Too many if’s,
and’s and or’s to deal with I guess. I’m having to back out days if, let’s
say input1 is greater then the start rental date or less then the date in
service. Same for input2 and the stop rental date and the date sold.

KARL DEWEY said:
Try this --
SELECT JRyhmes.InService, JRyhmes.StartRentalDate, JRyhmes.StopRentalDate,
JRyhmes.DateSold, DateDiff("d",[StartRentalDate],[StopRentalDate]) AS [Rental
Days], DateDiff("d",[InService],IIf([DateSold] Is
Null,[StopRentalDate],[DateSold])) AS [Days On-hand],
(DateDiff("d",[StartRentalDate],[StopRentalDate]))/(DateDiff("d",[InService],IIf([DateSold]
Is Null,[StopRentalDate],[DateSold])))*100 AS [Percent Utilization]
FROM JRyhmes;


:

I have tried every formula and method I can think of and decide to turn it
over to the experts. I’m trying to get a percent utilization based on a date
range (sent to the query from a form) using Input1 for beginning date and
Input2 for the end date. Below is an example of the date.
Lets say:
Input1 = 1/1/2002
Input2 = 12/31/2004



InService StartRentalDate StopRentalDate DateSold
1/1/1998 1/1/1998 2/19/2003 2/15/2006
1/1/1998 2/19/2003
1/1/1998 4/14/2002 4/15/2002
1/1/1998 2/20/2003 2/16/2004 2/15/2006
2/20/2003 2/16/2004
8/1/1999 9/18/2001 2/15/2002
8/1/1999 9/18/2001 2/15/2002
6/1/1997 10/10/2000 2/5/2002
Click to expand...
Click to expand...
Click to expand...
 

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

Select Max date from three different columns 2
WCG Stats Sunday 10 September 2023 3
WCG Stats Friday 29 September 2023 4
WCG Stats Monday 13 February 2023 3
WCG Stats Friday 17 February 2023 2
WCG Stats Saturday 24 June 2023 3
WCG Stats Thursday 28 September 2023 4
WCG Stats Saturday 09 September 2023 4

Top