counting problem

[Ian]

I need help with the logic of this problem: I am trying to determine how
long people wait for an appointment. The db records the each appt with a
unique apptID, appttype, apptdate (amoung other variables). I need to
organize my results so that the query will show:
a) Each date an appointment occured.
b) A count of the number of appointment that occured in the 30 days prior to
the specificed date (CountPre)
c) A count of the number of appointments that were in the system on or after
the specified date (CountPost)

What I want to end up with is a list like this:
Date CountPre CountPost
January 1 30 60
January 2 25 53
January 3 27 72

For the life of me I can't wrap my head around how to structure the query.
Any help is always appreciated. thx. Ian

 

[KARL DEWEY]

I am trying to determine how long people wait for an appointment.
Your field list indicates only one date - apptdate. You need two dates to
determine the waiting time.
Maybe an ApplyDate/RequestDate.

I do not know your business rules - are appointments made immediately or
some time later? If later the use this ---
DateDiff("d", IIF([apptdate] Is Null, Date(), [apptdate]), [ApplyDate])

 

[John Spencer]

Possibly something like the following

SELECT Distinct [Date]
, (SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] Between YT.Date -30 and YT.Date -1) as Pre
, SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] >=YT.Date) as Post
FROM YourTable as YT


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

 

[Ian]

Thanks guys I'll give both a try tonight. The problem is that on any given
day there is going to be a different number of appointments both before and
after. So I'm actually going to use the [CreateDate] field. so for a given
appt date I want it to count the number of appointments with an ApptDate in
the previous 30 days then count the number of appointments going forward with
a) a CreateDate on or before the date i'm interested in and an apptdate on or
after the date i'm interest in.

I know it sounds confusing. If it helps I'm trying to apply something
called Little's Law to determine the average wait time for an appointment.
LL=total number waiting/average completion rate.

thanks Ian.

Possibly something like the following

SELECT Distinct [Date]
, (SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] Between YT.Date -30 and YT.Date -1) as Pre
, SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] >=YT.Date) as Post
FROM YourTable as YT


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

I need help with the logic of this problem: I am trying to determine how
long people wait for an appointment. The db records the each appt with a
unique apptID, appttype, apptdate (amoung other variables). I need to
organize my results so that the query will show:
a) Each date an appointment occured.
b) A count of the number of appointment that occured in the 30 days prior
to
the specificed date (CountPre)
c) A count of the number of appointments that were in the system on or
after
the specified date (CountPost)

What I want to end up with is a list like this:
Date CountPre CountPost
January 1 30 60
January 2 25 53
January 3 27 72

For the life of me I can't wrap my head around how to structure the query.
Any help is always appreciated. thx. Ian

 

[Ian]

Hey John -- I think you're solution is on the right track. Right now I've
got the statement here:
SELECT DISTINCT [ApptDate]
, (Select Count(apptID)
FROM dbo_Appt as Tmp
WHERE Tmp.[ApptDate] between YT.ApptDate-30 and YT.ApptDate-1) AS Pre
, (Select Count(apptID)
FROM dbo_Appt as Tmp
WHERE Tmp.[Apptdate] >= YT.ApptDate) AS Post
FROM dbo_Appt AS YT;

but it loops or something and I don't get any result. at one point I was
getting the distinct dates but not the counts.

I've written 3 queries which give me the correct answers (I need to condense
them to one as this is ultimately going to end up in a pivottable SQL
statement from a dashboard). Here are the 3 queries that give the correct
output but only for a single day (in this case July 1, 2007)

QUERY1
‘Count all appointments during 30days prior to July 1
‘Query is called LittlePredateCount
SELECT Count(dbo_Appt.ApptID) AS CountOfApptID
FROM dbo_Appt
WHERE (((dbo_Appt.ApptDate) Between ((#7/1/2007#)-30) And #7/1/2007#) AND
((dbo_Appt.ApptTypeID)=2) AND ((dbo_Appt.OfficeID)=1) AND
((dbo_Appt.ApptStatus)=3 Or (dbo_Appt.ApptStatus)=4));
‘Answer is 84

QUERY2
‘Days available per 30 = 19.38 (252/52*4)
‘Query is called LittleACR
Calculate average completion rate
SELECT [CountOfApptID]/19.38 AS Average
FROM LittlePreDateCount;
‘Answer is 4.33436532508

QUERY3
‘Count appointments on or after date
‘Query is called LittlePostDateCount
SELECT Count(dbo_Appt.ApptID) AS CountOfApptID
FROM dbo_Appt
WHERE (((dbo_Appt.ApptDate)>=#7/1/2007#) AND
((dbo_Appt.CreateDate)<=#7/1/2007#) AND ((dbo_Appt.ApptTypeID)=2) AND
((dbo_Appt.OfficeID)=1) AND ((dbo_Appt.ApptStatus)=3 Or
(dbo_Appt.ApptStatus)=4));
‘Answer is 115

Possibly something like the following

SELECT Distinct [Date]
, (SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] Between YT.Date -30 and YT.Date -1) as Pre
, SELECT Count(*)
FROM YourTable as Tmp
WHERE Tmp.[Date] >=YT.Date) as Post
FROM YourTable as YT


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

I need help with the logic of this problem: I am trying to determine how
long people wait for an appointment. The db records the each appt with a
unique apptID, appttype, apptdate (amoung other variables). I need to
organize my results so that the query will show:
a) Each date an appointment occured.
b) A count of the number of appointment that occured in the 30 days prior
to
the specificed date (CountPre)
c) A count of the number of appointments that were in the system on or
after
the specified date (CountPost)

What I want to end up with is a list like this:
Date CountPre CountPost
January 1 30 60
January 2 25 53
January 3 27 72

For the life of me I can't wrap my head around how to structure the query.
Any help is always appreciated. thx. Ian