Calculating an average daily differnce 1

[Chummly66]

Not sure if this is simple or difficult because I really am not sure how to go about this. So, the best way for me to explain is by the example below.

I track repair work for multiple locations. This is tracked by the field "StoreID". I also track the date a repair is first taken in by that location "DateTakeinIn" and track the date it arrives at the repair shop, "DateToRepair".

What I would like to do, is get a breakdown by month, for each Store Location, the average difference between the Date Taken In, vs. the Date To Repair.

Example:

Store "A" took in 5 repairs for September:

Date Taken In Date To Repair
9/1/06 9/8/06
9/3/06 9/8/06
9/9/06 9/21/06
9/10/06 9/21/06
9/15/06 9/21/06

So the date differences would be 8, 5, 12, 11, 6 and the average difference would be: 8.40. The 8.40 is what I am looking for.

Any help or suggestions would be very much appreciated!

Thanks,
Richard

 

[Golom]

Select StoreID
     , AVG(DateDiff("d", [Date Taken In], [Date To Repair])) As [AvgTime]

From myTable

Group By StoreID

 

[Chummly66]

Golom,

Thanks for the assistance. But is there a way to run that query without having to enter in the date? I would like it broken down by month, so I could just enter in "8" for august and have it automatically select all records with a DateTakenIn for august.

Then again, I would probably have to also take into account thise with a DateTakenIn of 8/31 and a Datetorepair of 9/5 - i would imagine that messing up the calculations?

Thanka Again!
Richard

 

[Golom]

Not quite sure what you mean. That query doesn't require that you enter a date (assuming that [Date Taken In] and [Date To Repair] are fields in your table.)

Select StoreID
     , Year([Date Taken In]) As [The Year]
     , Month([Date Taken In]) As [The Month]
     , AVG(DateDiff("d", [Date Taken In], [Date To Repair])) As [AvgTime]

From myTable

Where Month([Date Taken In]) BETWEEN 
      [Enter the First Month] AND [Enter the Last Month]

Group By StoreID, Year([Date Taken In]), Month([Date Taken In])

which will include records such as you suggest ([Date Taken In] in one month and [Date To Repair] in the next.)

Select StoreID
     , Year([Date Taken In]) As [The Year]
     , Month([Date Taken In]) As [For Month]
     , AVG(DateDiff("d", [Date Taken In], [Date To Repair])) As [AvgTime]

From myTable

Where Month([Date Taken In]) BETWEEN 
      [Enter the First Month] AND [Enter the Last Month]

  AND Month([Date To Repair]) BETWEEN 
      [Enter the First Month] AND [Enter the Last Month]

Group By StoreID, Year([Date Taken In]) , Month([Date Taken In])

and that requires that both dates are in the same month.
Note however that records that span months will not appear at all for repairs that were taken in in the last month and were sent to repair in the one after it.


I added the "Year" stuff to ensure that you don't report statistics from different years together.

I would also advise that you use controls on a form such as text boxes to supply the parameters. The above will pop up an inputbox for each of the parameters.

 

[Chummly66]

Golom,

Thanks again for the assistance. I used rthe first code in your last replay and it worked exactly like I needed.

Thank you again!
Richard