saison help

[J.J.]

hi
I have a table with saison_Id, saison_begin and saison_end in which i have 9
records or 9 saisons.
in secodnd table i enter guest_id, guest_name, guest_arrival and
guest_departure.
now i need a query that calculates how many days did the guest stayed by
saisons.
example:
first saison is form 17/07 till 14/08
second saison is form 14/08 till 21/08
so if the guest arrived 24/07 and departured 31/07 then it's 7 days in first
saison
or if the guests arrived 07/08 ad departured 21/08 then it's 7 days in first
and 7 days in sacond saison.
any suggestion how should i solve this problem?
thanks in advance
J.J.

 

[Michel Walsh]

Hi,

If you have a table that delimits the season:

Seasons ' table name
Starting, Ending Season ' fields name
17.07.2004 14.08.2004 M
14.08.2004 21.08.2004 N ' data


If the guess arrived at A and leave at Z, then, the interval [A, Z]
overlap, in part or in total, a range (a row) in the table Seasons if and
only if

A < Ending AND Z >= Starting

and the interval of common time is:

iif(Z<Ending, Z, Ending) - iif(A>= Starting, A, Starting)


(you may have to add 1 to the result, that is dependant about how you
count).

So, if you have

Clients
ClientID, A, Z ' field name


SELECT ClientID, Season,
iif(Z<Ending, Z, Ending) - iif(A>= Starting, A, Starting)
AS lengthOfTime
FROM Clients INNER JOIN Seasons
ON a<Ending AND z>= starting



should supply you with the non-aggregated data, so you can check the formula
are ok, then


SELECT Season,
SUM( iif(Z<Ending, Z, Ending) - iif(A>= Starting, A, Starting))
AS sumOfTime
FROM Clients INNER JOIN Seasons
ON a<Ending AND z>= starting
GROUP BY Season



would give you the frequentation, by season.




Hoping it may help,
Vanderghast, Access MVP