Calculation Challenge

[Guest]

This is a calculation challenge for a non profit to calculate how many nights
during a given month did people stay in there shelter.

This is the challenge. If I get the Total Nights to calculate based on
[CheckOut]-[CheckIn], how can I have it only calculate the days they stayed
in this case June. See Example below:

In number one they have not check out yet so I need it to calculate against
the last day of the month in June.

Number 2 and 3 calculate correctly

In Number 4 the Check In Date is in May but I only want it to count Jun 1 to
June 2.

Check In Check Out Total Nights
1. 6/1/2006
2. 6/3/2006 6/6/2006 3
3. 6/5/2006 6/10/2006 5
4. 5/25/2006 6/2/2006 8

 

[Duane Hookom]

Did you create a table of dates as I suggested to you in another thread in
another news group?

 

[Guest]

I think this will do it. Create a table named CountNumber with a field named
[CountNUM] that has integers 0 through your maximum date span. Use the three
queries below and replace table William with your table and use your field
name instead of Pt#]. Substitute your date fields for [Start dt] and [End
dt].

QUERY NAMED William-1 --
SELECT William.[Pt#], William.[Start dt], William.[End dt],
CVDate(Format(DateAdd("m",[CountNUM],[Start dt]),"yyyy/mm/""01""")) AS [Month
of stay]
FROM William, CountNumber
WHERE (((DateAdd("m",[CountNUM],[Start dt]))<=[End dt]))
ORDER BY William.[Pt#], DateAdd("m",[CountNUM],[Start dt]);

QUERY NAMED William-2 --
SELECT Q.[Pt#], Q.[Start dt], Q.[End dt], Q.[Month of stay], (SELECT
COUNT(*) FROM [William-1] Q1
WHERE Q1.[Pt#] = Q.[Pt#]
AND Q1.[Month of stay] < Q.[Month of stay])+1 AS [Month]
FROM [William-1] AS Q
ORDER BY Q.[Pt#], Q.[Month of stay];

QUERY NAMED William-3 --
SELECT [William-2].[Pt#], [William-2].[Start dt], [William-2].[End dt],
[William-2].[Month of stay], [William-2].Month,
IIf([Month]=1,DateDiff("d",[Start dt],IIf([End dt]<DateAdd("m",1,[Month of
stay])-1,[End dt],DateAdd("m",1,[Month of stay])-1)),IIf([End
dt]<DateAdd("m",1,[Month of stay])-1,DateDiff("d",[Month of stay],[End
dt]),DateDiff("d",[Month of stay],DateAdd("m",1,[Month of stay])))) AS [Days
stay]
FROM [William-2];

 

[John Spencer]

If you are trying to do this for just one period, then you can use two
parameters - a start date and an end date for the period and a calculation.

Assumptions:
there will always be a START DATE.
You are using parameters (or a form) to specify the period (a month)

SELECT IDNumber
, CheckIn
, Checkout
, DateDiff("d"
, IIF(Checkin < [Enter Start Date],[Enter Start Date])
, IIF(Nz(Checkout,[Enter End Date])>=[Enter End Date]
,[Enter End Date]
,Checkout)) as TotalNights
FROM SomeTable
WHERE CheckIn <= [Enter End Date] and
(CheckOut >= [Enter Start date] Or CheckCout is Null)