I need to calculate the average days open based upon user and problem

[trwebster]

I have a query that creates a list of cases and how many days it took to close the case. I need to calculate the average number of days it took to close a case based upon the user and the problem type.

Here's a sample of the data:
(This is what I need)
Advocate ProblemGroup #daysToClose AvgDaysToClose
Betty Housing 4 6.333
Betty Housing 7 6.333
Betty Housing 6 6.333
Betty Consumer 5 7.333
Betty Consumer 8 7.333
Betty Consumer 9 7.333

Her is my SQL statement that gets me the first 3 columns:

SELECT [trw Users].AdvocateLNFN, menu_problem.ProblemGroup, cases.open_date, cases.close_date, DateDiff("d",[open_date],[close_date]) AS [#ofDaysOpen]
FROM [trw Users] RIGHT JOIN (menu_problem RIGHT JOIN cases ON menu_problem.value = cases.problem) ON [trw Users].user_id = cases.user_id
WHERE (((cases.close_date) Between [Begin Date] And [End Date]))
ORDER BY [trw Users].AdvocateLNFN, menu_problem.ProblemGroup;

What do I need to do to calculate the average days to close?
Any help will be greatly appreciated.

 

[ByteMyzer]

How about:

SELECT [trw Users].AdvocateLNFN, menu_problem.ProblemGroup, cases.open_date, cases.close_date, DateDiff("d",[open_date],[close_date]) AS [#ofDaysOpen], sqry1.[#AvgDaysOpen]
FROM ((menu_problem INNER JOIN cases ON menu_problem.value = cases.problem)
INNER JOIN [trw Users] ON [trw Users].user_id = cases.user_id)
INNER JOIN (SELECT [trw Users].AdvocateLNFN, menu_problem.ProblemGroup, Avg(DateDiff("d",[open_date],[close_date])) AS [#AvgDaysOpen]
FROM (menu_problem INNER JOIN cases ON menu_problem.value = cases.problem)
INNER JOIN [trw Users] ON [trw Users].user_id = cases.user_id
GROUP BY [trw Users].AdvocateLNFN, menu_problem.ProblemGroup) AS sqry1 ON [trw Users].AdvocateLNFN=sqry1.AdvocateLNFN AND menu_problem.ProblemGroup=sqry1.ProblemGroup
ORDER BY [trw Users].AdvocateLNFN, menu_problem.ProblemGroup;

 

[MichaelRed]

TRANSFORM Avg(tblTrwUsers.DaysToClose) AS AvgOfDaysToClose
SELECT tblTrwUsers.Advocate, Avg(tblTrwUsers.DaysToClose) AS [Total Of DaysToClose]
FROM tblTrwUsers
GROUP BY tblTrwUsers.Advocate
PIVOT tblTrwUsers.ProblemGrooup;
[code]

but the avg for the housing group is wrong in one of the sets (you 6.3333 seems like it should be 5.3333 both by mechanical calc (17 total days to close for 3 cases) and in the computed value set.

[code]
Advocate	Total Of DaysToClose	Consumer	Housing
Betty	6.5	7.33333333333333	5.66666666666667

from the posted SQL statement.

Please note that I just construced a table with your sample data, so you need to study / understand the concept of the crosstab query and apply it to your actual data.

I also observe that you are using some practices which are generally discouraged (field names with spaces)




MichaelRed