query to find average visits per day of week

[Zorro1265]

I have a table with visits entered and the date of visit. I need to be able to show the average number of visits per day of the week. We want to look at our data and see what day is busiest. I am doing this all in the query builder. When I use the SQL view I get this string if it helps at all.

SELECT tblPatientEncounter.Date, tblPatientEncounter.TTEcho, tblPatientEncounter.Facility, DatePart("w",[Date]) AS Dates
FROM tblPatientEncounter
WHERE (((tblPatientEncounter.Date)<#1/1/2006#) AND ((tblPatientEncounter.TTEcho)=-1))
ORDER BY tblPatientEncounter.Date DESC;

Any ideas? I think I am close since my output give me each visit and the day of the week I just need to group and average them by the date and day.

Thanks


Zorro

 

[rac2]

The tricky part here will be knowing the number of weeks involved in the average per day of the week.

You will need to count the number of visits per day of the week and divide by the number of weeks.

The day of the week can be obtained with the WEEKDAY() function and will be used to GROUP BY.

SELECT WEEKDAY(Date), COUNT(*)
FROM tblPatientEncounter
WHERE Date < #1/1/2006#
  AND TTEcho = -1
GROUP BY WEEKDAY(Date)

This will yield seven rows with the number of encounters for each day of the week.

The number of Mondays, etc., on which there were visits is the difficult piece. Do you wish to count holidays? Probably not, there will not be any visits on holidays, right? Are there days which are not holidays and which have no visits? Do you wish to include these days in the average?

If there is at least one visit every day you are open then you can use the DATEPART() function to count weeks.

SELECT DISTINCT DATEPART("ww",Date)
FROM FROM tblPatientEncounter
WHERE Date < #1/1/2006#

The number of rows yielded by that query would be the number of weeks prior to Jan 1, 2006. Unless of course dates for 2005 are possible, then you would need to include the year in the SELECT list, DATEPART("yyyy",Date).

However, this will be the wrong number if you wish to exclude holidays.

The total visits per day and week might be useful.

SELECT DATEPART("yyyy",Date),
       DATEPART("ww",Date),
       WEEKDAY(Date), COUNT(*)
FROM tblPatientEncounter
WHERE Date < #1/1/2006#
  AND TTEcho = -1
GROUP BY DATEPART("yyyy",Date),
         DATEPART("ww",Date),
         WEEKDAY(Date)

From this it should not be too much time to count the number of weeks with visits for each day of the week and use that with the totals by day of the week from the first query for your analysis.


This is not at all a nice answer. I hope someone else will show us the elegant solution to this problem.

 

[cmmrfrds]

add a group by clause.

SELECT tblPatientEncounter.facility,
Format(tblPatientEncounter.adate,"ddd") AS aday,
Count(Format(tblPatientEncounter.adate,"ddd")) as daycnt
FROM tblPatientEncounter
Group By facility, Format(tblPatientEncounter.adate,"w"), Format(tblPatientEncounter.adate,"ddd")

 

[Zorro1265]

Thanks for the help!

Zorro