Date Range

[MRCUSA]

Hi
I have a database for rentals, with an arrival date field and a departure
date field. It is easy to count the days between one and the other.
The question is, if an arrival date is Feb 23 and Departure March 15, from
the 20 days difference, I just want to count the days stayed this month,
March.
How can I write a query to return this please?
Thanks in advance!
MRC

 

[fredg]

Hi
I have a database for rentals, with an arrival date field and a departure
date field. It is easy to count the days between one and the other.
The question is, if an arrival date is Feb 23 and Departure March 15, from
the 20 days difference, I just want to count the days stayed this month,
March.
How can I write a query to return this please?
Thanks in advance!
MRC

Watch the word wrap.
To count the days in February (the Arrival Month):

InFebateDiff("d",[Arrival],IIf(Month([Departure]) <>
Month([Arrival]),DateSerial(Year([Arrival]),Month([Arrival])+1,0),[Departure]))

To count the days in March (the Departure Month):

InMarch: DateDiff("d",IIf(Month([Departure])<>Month([Arrival]),
DateSerial(Year([Departure]),Month([Departure]),1),[Arrival]),[Departure])

 

[MRCUSA]

Hi Fred
Thank you!
Almost there. For March, it counted arrivals such as 7 Mar to 15 Mar just
fine, but arrival 30 Mar to 14 Apr was counted as counted as 13 instead of 2
as it should be.
MRC

 

[fredg]

Hi Fred
Thank you!
Almost there. For March, it counted arrivals such as 7 Mar to 15 Mar just
fine, but arrival 30 Mar to 14 Apr was counted as counted as 13 instead of 2
as it should be.
MRC

To count the days in March (the Departure Month):

InMarch: DateDiff("d",IIf(Month([Departure])<>Month([Arrival]),
DateSerial(Year([Departure]),Month([Departure]),1),[Arrival]),[Departure])

SELECT tblArriveDepart.Arrival, tblArriveDepart.departure,
DateDiff("d",[Arrival],IIf(Month([Departure])<>Month([Arrival]),DateSerial(Year([Arrival]),Month([Arrival])+1,0),[Departure]))
AS DaysInArriveMonth,
DateDiff("d",IIf(Month([Departure])<>Month([Arrival]),DateSerial(Year([Departure]),Month([Departure]),1),[Arrival]),[Departure])
AS InDepartMonthh
FROM tblArriveDepart;


Here is the copied and pasted result from my test query using the
above query SQL.

Arrival departure DaysInArriveMonth InDepartMonthh
3/7/2008 3/15/2008 8 8
3/30/2008 4/14/2008 1 13


Access counts the day crossings.
For the preriod 3/7 to 3/15
there are 8 crossings.
As you can see above the query returns 8.

Regarding the dates of 3/30 to 4/14:
For the Arrival month of March the difference in days between 3/30 and
3/31 is 1 (it crosses midnight once).

The difference between 4/1/ to 4/14 is 13.

As you can see above the query returns 1 and 13, which are correct.

 

[Michael Gramelspacher]

Hi
I have a database for rentals, with an arrival date field and a departure
date field. It is easy to count the days between one and the other.
The question is, if an arrival date is Feb 23 and Departure March 15, from
the 20 days difference, I just want to count the days stayed this month,
March.
How can I write a query to return this please?
Thanks in advance!
MRC

Use a calendar table (google it):

CREATE TABLE Calendar
(
calendar_date DATETIME NOT NULL PRIMARY KEY
);

CREATE TABLE Trips
(
person_id VARCHAR (10) NOT NULL,
trip_no VARCHAR (10) NOT NULL,
departute_date DATETIME NOT NULL,
return_date DATETIME NULL,
PRIMARY KEY (person_id, trip_no)
);

INSERT INTO Trips Values('1','1',#2/18/2008#,#2/26/2008#);
INSERT INTO Trips Values('1','2',#2/28/2008#,#3/18/2008#);
INSERT INTO Trips Values('2','1',#2/10/2008#,#3/5/2008#);
INSERT INTO Trips Values('2','2',#3/15/2008#,#4/2/2008#);

SELECT T.person_id,
T.trip_no,
FORMAT(C.calendar_date,"mmm yyyy") AS [Month],
COUNT(C.calendar_date) AS Days
FROM Calendar AS C,
Trips AS T
WHERE (((C.calendar_date) BETWEEN T.departure_date
AND T.return_date))
GROUP BY T.person_id,T.trip_no,FORMAT(C.calendar_date,"mmm yyyy");

person_id trip_no Month Days
1 1 Feb 2008 9
1 2 Feb 2008 2
1 2 Mar 2008 18
2 1 Feb 2008 20
2 1 Mar 2008 5
2 2 Apr 2008 2
2 2 Mar 2008 17

 

[MRCUSA]

Hi Fred

That works great.

Thank you for helping me with this.

MRC

 

[MRCUSA]

Sorry Fred
The example arrival 3/30/08, the result needs to be 2.
The guests arrived the 30th and stayed the 30th and 31st in the unit,
therefore 2 nights in March.
They stayed a total of 15 nights, 2 in March and 13 in April
MRC

 

[fredg]

Sorry Fred
The example arrival 3/30/08, the result needs to be 2.
The guests arrived the 30th and stayed the 30th and 31st in the unit,
therefore 2 nights in March.
They stayed a total of 15 nights, 2 in March and 13 in April
MRC

SELECT tblArriveDepart.Arrival, tblArriveDepart.departure,
DateDiff("d",[Arrival],IIf(Month([Departure])<>Month([Arrival]),DateSerial(Year([Arrival]),Month([Arrival])+1,0),[Departure]))
AS DaysInArriveMonth,
DateDiff("d",IIf(Month([Departure])<>Month([Arrival]),DateSerial(Year([Departure]),Month([Departure]),1),[Arrival]),[Departure])
AS InDepartMonthh
FROM tblArriveDepart;


Here is the copied and pasted result from my test query using the
above query SQL.

Arrival departure DaysInArriveMonth InDepartMonthh
3/7/2008 3/15/2008 8 8
3/30/2008 4/14/2008 1 13

You requested to show just the elapsed days of the departure month,
not the arrival month,so that part is working as you requested.
You can change the Arrival month code, to add the 1 extra day.

Select .... etc ....
DateDiff("d",[Arrival],IIf(Month([Departure])<>Month([Arrival]),
DateSerial(Year([Arrival]),Month([Arrival])+1,0)+1,[Departure]))
AS DaysInArriveMonth, etc...

Note the +1 after ... ,Month([Arrival])+1,0)