RE: Tricky Calculation Question

Dale

I used the following language
SELECT qryUtilization1.Prm_Code, qryUtilization1.Census,
qryUtilization1.CensusDate, [tblCapacity].[Cap]/[qryUtilization1].[Cap] AS
Utilization
FROM qryUtilization1 INNER JOIN tblCapacity ON qryUtilization1.Prm_Code =
tblCapacity.Prm_Code
WHERE (((qryUtilization1.CensusDate)>=[tblCapacity].[CapStartDate] And
(qryUtilization1.CensusDate)<=[tblCapacity].[CapEndDate]));

The query works fine and gives the appropriate number of records when I run
it without the WHERE statement but I get no results with the where statement.
The tblCapacity has all my programs with a CapStartDate of 10/1/2004 and a
null CapEndDate because the Cap is still current.

Ideas?

Thanks
--
Thanks

You all are teaching me so much


"Dale Fye" wrote:

you forgot the query.

What I would do is have an additional table that contains the ProviderID,
Capacity, CapStartDate, and CapEndDate. When you start a new capacity, you
give it a start date, then leave the end data as NULL.

Then you create a query that contains the census values and this Capacity
table. Something like:

SELECT Census.ProviderID,
Average(Census.[Census]/Capacity.[Capacity]) as Utilization
FROM Census
INNER JOIN Capacity
ON CENSUS.ProviderID = Capacity.ProviderID
WHERE Census.CensusDate >= Capacity.CapStartDate
AND Census.CensusDate <= Capacity.CapEndDate
GROUP By Census.ProviderID

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"knowshowrosegrows" wrote:

I have a database that tracks daily census information for a group of service
providers. It is a pretty simple thing. For an example, a group home has a
capacity (CAP) of 12 and every day they report what their census is - say 11.
I do utilization reports where I divide their capacity by their census and
come up with a percentage for how well their program is utilized.

The question I have is that every now and then we change the capacity for
their program. That capacity is fixed in time and should not change their
utilization percentage for all the days they reported before we changed their
capacity.

Below is the sql for the query I use to make a utilization report. Can
someone suggest ways I can relate the capacity to the date?
--
Thanks

You all are teaching me so much
.

Relevant Pages

  • Re: Can I do this Query?
    ... The capacity will never exceed 1 for any item. ... Can I get the query to return this information? ... SELECT Orderid,MAX(Line)Line FROM #Test GROUP BY Orderid ...
    (microsoft.public.sqlserver.server)
  • How do I calculate totals in a query
    ... Attached is the sql view. ... Capacity Known QUERY Query The Real Deal].Detailer, ... Capacity Known QUERY Query The Real Deal].Description, [Zee's Schedule + ...
    (microsoft.public.access.queries)
  • Re: Tricky Calculation Question
    ... You were comparing a date to a NULL, and that comparison will always be ... Capacity, CapStartDate, and CapEndDate. ... Then you create a query that contains the census values and this ... I do utilization reports where I divide their capacity by their ...
    (microsoft.public.access.queries)
  • Re: Can I do this Query?
    ... Andrew and Uri ... Order No Line No Box Capacity ... Can I get the query to return this information? ... SELECT Orderid,MAXLine FROM #Test GROUP BY Orderid ...
    (microsoft.public.sqlserver.server)
  • Re: CQWP and ME
    ... the query will actually provide as a radio button after I filter by Assigned To. ... Additional performance and capacity planning factors (Office SharePoint ...
    (microsoft.public.sharepoint.portalserver)
Loading