same result using avg and max 1

[sbbrown9924]

I am developing waiting room statistics for an Emergency Room according to the patient's acuity. In this non-acute query, the query should determine the average and maximum wait times; howewer, I am getting the same result for both the avg and max values. The value for both is the max value. The same thing happens with the acute query too. What am I missing here? Thanks.

<%
Dim rsNonAcuteWaitingRoomPatients
Dim rsNonAcuteWaitingRoomPatients_cmd
Dim rsNonAcuteWaitingRoomPatients_numRows

Set rsNonAcuteWaitingRoomPatients_cmd = Server.CreateObject ("ADODB.Command")
rsNonAcuteWaitingRoomPatients_cmd.ActiveConnection = MM_TrackingConnect_STRING
rsNonAcuteWaitingRoomPatients_cmd.CommandText = "SELECT COUNT(VisitID) AS [ctNonAcuteWaitingRoomPatients], avg(DateDiff('n',GreetTime,now())) AS [avgNonAcuteGreetToNowTime], avg(DateDiff('n',TriageTime,Now())) AS [avgNonAcuteTriageToNowTime], max(DateDiff('n',GreetTime, Now())) AS [maxNonAcuteGreetToNowTime], max(DateDiff('n', TriageTime,Now())) AS [maxNonAcuteTriageToNowTime], LastName, FirstName, GreetTime, TriageTime,InRoomTime,RoomOrderId, TriageAcuity, Initial_LocationID FROM TrackingMain WHERE (RoomOrderID=485 OR RoomOrderID=486 OR RoomOrderID=370) AND (TriageAcuity=40 OR TriageAcuity=50) GROUP BY LastName, FirstName,GreetTime, TriageTime,InRoomTime,RoomOrderId, TriageAcuity, Initial_LocationID"
rsNonAcuteWaitingRoomPatients_cmd.Prepared = true

Set rsNonAcuteWaitingRoomPatients = rsNonAcuteWaitingRoomPatients_cmd.Execute
rsNonAcuteWaitingRoomPatients_numRows = 0
%>

This is for display. The function convertHRMin function
takes minute output and converts it to dd:hh:mm format.

for average:
<%if IsNull(rsNonAcuteWaitingRoomPatients.Fields.Item("avgNonAcuteGreetToNowTime").Value) Then CALL convertHrMin(rsNonAcuteWaitingRoomPatients.Fields.Item("avgNonAcuteTriageToNowTime").Value) Else CALL convertHrMin(rsNonAcuteWaitingRoomPatients.Fields.Item("avgNonAcuteGreetToNowTime").Value)%>

for maximum:
<%if IsNull(rsNonAcuteWaitingRoomPatients.Fields.Item("maxNonAcuteGreetToNowTime").Value) Then CALL convertHrMin(rsNonAcuteWaitingRoomPatients.Fields.Item("maxNonAcuteTriageToNowTime").Value) Else CALL convertHrMin(rsNonAcuteWaitingRoomPatients.Fields.Item("maxNonAcuteGreetToNowTime").Value)%>

Here is the convertHrMin function..

<%
'Function to convert minute output to a format of 00d:00m:00h
Function convertHrMin(nTime)
If IsNull(ntime) Then
ntime=0
End If
iHour = Round(nTime/60,1)
If InStr(iHour,".") = 0 Then
iHrTime = iHour
Else
iHrTime = Left(iHour, InStr(iHour,".")-1)
End If
If iHrTime < 24 Then
Response.Write FormatDateTime(nTime/1440,4)
Else If iHrTime = 24 Then
Response.Write "1:"& FormatDateTime(nTime/1440,4)
Else If iHrTime > 24 Then
iNoDays = iHrTime/24
If InStr(iNoDays,".") = 0 Then
iNoDays = iNoDays
Else
iNoDays = Left(iNoDays, InStr(iNoDays,".")-1)
End If
If Len(iNoDays) = 1 Then
Response.Write "0" & iNoDays & ":" & FormatDateTime(nTime/1440,4)
Else
Response.Write iNoDays & ":" & FormatDateTime(nTime/1440,4)
End If
Else
End If
End If
End If
End Function
%>

 

[Golom]

Its because your GROUP BY clause includes GreetTime and TriageTime and those are the fields that you are trying to get AVG and MAX values for. With those fields in the GROUP BY, each GROUP will probably have only one record in it and the AVG and MAX values will be the same.

As it stands, you are computing COUNT, AVG and MAX for each unique combination of the fields
[tt]
LastName
FirstName
GreetTime
TriageTime
InRoomTime
RoomOrderId
TriageAcuity
Initial_LocationID
[/tt]
I would be suprised if, with all those fields in the group, you ever got a group with more than one record in it. See what you get with something like

rsNonAcuteWaitingRoomPatients_cmd.CommandText = _
 "SELECT COUNT(VisitID) AS [ctNonAcuteWaitingRoomPatients], " & _
 "AVG(DateDiff('n',GreetTime ,Now())) AS [avgNonAcuteGreetToNowTime], " & _
 "AVG(DateDiff('n',TriageTime,Now())) AS [avgNonAcuteTriageToNowTime], " & _
 "MAX(DateDiff('n',GreetTime ,Now())) AS [maxNonAcuteGreetToNowTime], " & _
 "MAX(DateDiff('n',TriageTime,Now())) AS [maxNonAcuteTriageToNowTime], " & _
 "LastName, FirstName " & _
    "FROM TrackingMain " & _
    "WHERE RoomOrderID  IN (485,486,370) " & _
    "  AND TriageAcuity IN (40,50) " & _
    "GROUP BY LastName, FirstName "

and add fields back into SELECT and GROUP BY as required.

 

[Golom]

Or possibly even

rsNonAcuteWaitingRoomPatients_cmd.CommandText = _
 "SELECT COUNT(VisitID) AS [ctNonAcuteWaitingRoomPatients], " & _
 "AVG(DateDiff('n',GreetTime ,Now())) AS [avgNonAcuteGreetToNowTime], " & _
 "AVG(DateDiff('n',TriageTime,Now())) AS [avgNonAcuteTriageToNowTime], " & _
 "MAX(DateDiff('n',GreetTime ,Now())) AS [maxNonAcuteGreetToNowTime], " & _
 "MAX(DateDiff('n',TriageTime,Now())) AS [maxNonAcuteTriageToNowTime], " & _
 "TriageAcuity " & _
"FROM TrackingMain " & _
"WHERE RoomOrderID  IN (485,486,370) " & _
"  AND TriageAcuity IN (40,50) " & _
"GROUP BY TriageAcuity "

since (I assume) "LastName, FirstName" is the patient's name and there is probably only one record for each patient. This version should give you two records for TriageAcuity = 40 and TriageAcuity = 50.

 

[sbbrown9924]

Golum;

You were way right - way to many GROUP BY fields.

Thanks - also, I didn't realize I could use IN instead of multiple OR's. I always learn something new from you. !!

Steve