Calculating sets of times

[Sarah G]

I have an expression in a query to calculate the time difference (in decimal
hrs) the length of time between arrival and departure time. this works fine
and have no problems. However, I need to run a query which will give me the
number of people who were in attendance <=2 hrs, <=4hrs, <=6hrs etc based on
that LOS calculation
Thank you

 

[Lord Kelvan]

are you looking for
<= 2
and

2 <= 4 and
4 <= 6

or just

<= for each because the <=4 will also include the <=2 count and so
will the <=6 count

something like this

SELECT "<=2" AS range, Count(losquery.los) AS numberofattendies
FROM losquery
WHERE (((losquery.los)<=2))
GROUP BY "<=2"
union all
SELECT "<=4" AS range, Count(losquery.los) AS numberofattendies
FROM losquery
WHERE (((losquery.los)<=4))
GROUP BY "<=4"
union all
SELECT "<=6" AS range, Count(losquery.los) AS numberofattendies
FROM losquery
WHERE (((losquery.los)<=6))
GROUP BY "<=6"

may be what you are looking for

or even


SELECT "<=2" AS range, Count(personname) AS numberofattendies
FROM staytable
WHERE (((departuretime-arrivaltime)<=2))
GROUP BY "<=2"
union all
SELECT "<=4" AS range, Count(personname) AS numberofattendies
FROM staytable
WHERE (((departuretime-arrivaltime)<=4))
GROUP BY "<=4"
union all
SELECT "<=6" AS range, Count(personname) AS numberofattendies
FROM staytable
WHERE (((departuretime-arrivaltime)<=6))
GROUP BY "<=6"


Hope this helps

Regards
Kelvan

 

[Sarah G]

Thank you so much. Both ways are good I'll try them both
Thanks again

 

[Duane Hookom]

You could also use the Partition() function like:
SELECT Partition(Int(DateDiff("n",-[BeginTime],[EndTime])/60),0,200,2) AS
Grouping, Count(BeginTime) AS NumOf
FROM tblTimes
GROUP BY Partition(Int(DateDiff("n",-[BeginTime],[EndTime])/60),0,200,2);

 

[Sarah G]

I used the first one and changed the query names etc and the result was
correct.
The test data I am using is
"MRN -1ST APPT" 2hrs
"MRN -2ND APPT 1.5hrs
"MRN -3RD APPT 2hrs
"MRN -4TH APPT 2hrs
"MRN -5TH APPT 3 hrs
"MRN - 6TH APPT 2hrs
First I left it as it was with just the <=criteria
The result was correct
<=2 was 5
<=4 was 6
<=4 was 6
In essence this is correct however I need it to be
Between >0<=2
Between >2<=4
Between >4<=6
I changed the criteria in the query and have ended up with the same 5,6,6
result.
SELECT "<=2" AS range, Count(loscalc.MRN) AS numberofattendies
FROM loscalc
WHERE (((loscalc.los)<=2))
GROUP BY "<=2"
union all
SELECT ">2<=4" AS range, Count(loscalc.MRN) AS numberofattendies
FROM loscalc
WHERE (((loscalc.los)>2<=4))
GROUP BY ">2<=4"
UNION ALL SELECT ">4<=6" AS range, Count(loscalc.MRN) AS numberofattendies
FROM loscalc
WHERE (((loscalc.los)>4<=6))
GROUP BY ">4<=6";

 

[Lord Kelvan]

yeah i thought that is what you wanted

SELECT "<=2" AS range, Count(loscalc.MRN) AS numberofattendies
FROM loscalc
WHERE (((loscalc.los)<=2))
GROUP BY "<=2"
union all
SELECT ">2<=4" AS range, Count(loscalc.MRN) AS numberofattendies
FROM loscalc
WHERE (((loscalc.los)>2 and loscalc.los <=4))
GROUP BY ">2<=4"
UNION ALL SELECT ">4<=6" AS range, Count(loscalc.MRN) AS
numberofattendies
FROM loscalc
WHERE (((loscalc.los)>4 and loscalc.los<=6))
GROUP BY ">4<=6";

that should do it

 

[John Spencer]

You could do the same thing with a little math

SELECT LOS\2 as HourRange, Count(MRN) as NumberAttendees
FROM Loscalc
GROUP BY LOS\2

That would give you Less than 2, 4, 6, 8, 10, etc.

If you really need to have less than or equal to then modify the
calculation by using something like the following
(Int(LOS-.0000001))\2

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================