Days per month

C

Chad

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
 
J

John W. Vinson

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.
Click to expand...

Umm?

You don't need tblMonth at all.

SELECT Format([ArrivalDate], "mmm") As TheMonth, DateDiff("d", [ArrivalDate],
[DepartureDate]) AS Days FROM tblReservation;

Apply criteria and grouping as needed.
 
C

Clifford Bass

Hi Chad,

Do they ever go through more than one month boundry crossing, such as
1/15/08 - 5/15/08? Or is there at most only one month boundary crossing?

Clifford Bass
 
C

Clifford Bass

Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass
 
D

Dale Fye

John,

I don't think DateDiff will do it, because it will give him the # of days
between the arrival and departure, which is 7. What he wants is the number
of days within each month that the reservation spans.

Chad,

I would do this with my numbers table (tbl_Numbers). It contains a single
field intNumber, which contains the values 0 through 9. I then create a
query (qry_Numbers) that looks like:

SELECT Hundreds.intNumber * 100 + Tens.intNumber * 10 + Ones.intNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

This will give you a query that contains the numbers 0 through 999. You
don't indicate whether any reservation will span a duration greater than
that, but I sincerely doubt it.

Now, the query that will return the results I think you are looking for
would look something like:

Select Temp.ReservationID,
Format(Temp.[ResDate], "mmm") as ResMonth,
Count(Temp.[ReservationID]) as Days
FROM
(SELECT ReservationID,
DateAdd("d", intNumber, [Arrival_Date]) as ResDate
FROM tblReservation, qry_Numbers
WHERE DateAdd("d", intNumber, [Arrival_Date]) <= [Departure_Date]) as Temp
GROUP BY Temp.[ReservationID],
Format(Temp.[ResDate], "mmm")

The subquery should give you a record for every date between the Arrival and
Departure date, for each reservation. Then the outer portion of the query
use Johns Format( ) function to group the results by month and count the # of
ResDays in each month.

If you only need this for a single reservation at a time, I recommend adding
a line to the WHERE clause of the sub-query to restrict the reservation
number .

You will probably need to play with the OrderBy clause, to get it to list
the months in numerical sequence. Something like:

ORDER BY Temp.ReservationID, Month([Temp.[ResDate])

But you may have to include this in the outer SELECT and/or the GroupBy
statement as well.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



John W. Vinson said:
Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.
Click to expand...

Umm?

You don't need tblMonth at all.

SELECT Format([ArrivalDate], "mmm") As TheMonth, DateDiff("d", [ArrivalDate],
[DepartureDate]) AS Days FROM tblReservation;

Apply criteria and grouping as needed.
Click to expand...
 
C

Chad

Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
Clifford Bass said:
Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

Chad said:
Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
 
D

Dale Fye

Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
Clifford Bass said:
Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

Chad said:
Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

Dale, thank you for your response. Your version is a very interesting way at
solving the problem I am facing! I was able to get your query to work and
return the numbers I need, however, I am having trouble wrapping my head
around the logic that is being used:

I understand that the subquery returns a list of every day in each
reservation and that the main SQL statement simply sums the days for each
month (which is pretty ingenious if you ask me) however it is not as
straightforward as I would like as I can picture myself coming back to this
in a few months and trying to figure out what I was doing...that being said,
I am keeping that as a viable option but I would also like to see if I can
figure out another method.

Thanks again for your help!
Chad

Dale Fye said:
Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
Clifford Bass said:
Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

:

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

I think you will find the method I provided is the simplest method. I use my
numbers table and query quite frequently when I'm working with dates. There
are a wide variety of other uses for it as well.

Right click on the query in the database window (or navpane). Select the
Object Properties, and copy the description that I gave you into the
description. This is a great way to document what a query does. I use it
all the time when I'm creating complex queries.

Good luck.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Dale, thank you for your response. Your version is a very interesting way at
solving the problem I am facing! I was able to get your query to work and
return the numbers I need, however, I am having trouble wrapping my head
around the logic that is being used:

I understand that the subquery returns a list of every day in each
reservation and that the main SQL statement simply sums the days for each
month (which is pretty ingenious if you ask me) however it is not as
straightforward as I would like as I can picture myself coming back to this
in a few months and trying to figure out what I was doing...that being said,
I am keeping that as a viable option but I would also like to see if I can
figure out another method.

Thanks again for your help!
Chad

Dale Fye said:
Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
:

Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

:

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

Dale,

After spending some time with it, I am feeling more comfortable with the
logic. The only problem I see with it is that when I run the query it is
taking up a lot of processing power and takes close to a minute to run

This is probably due to the number of reservations and the length of
stay...about 50,000 reservations at an average of about 5 days apiece, which
causes the subquery to return about 250,000 rows. Is there a way to make
this method more efficient?

If not, I was able to get my version to work correctly and it runs in less
than 5 seconds...in case it will help anyone, here is my SQL:

************************************************************
SELECT tblYears.Year_ID, tblMonths.Month_ID, tblMonths.Month, Nz((Select
SUM(([Departure Date] - [Arrival Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1))) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0) +

Nz((Select Sum(Day([Departure Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1,
1)-1)) As Days From tblReservations WHERE (Month([Departure Date])>Month_ID
AND Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)),0) AS Days_Reserved

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

Dale, thanks again for all of your help in this matter and I will definitely
be using the method you provided me with in many other situations.

Thanks!

Chad
Dale Fye said:
I think you will find the method I provided is the simplest method. I use my
numbers table and query quite frequently when I'm working with dates. There
are a wide variety of other uses for it as well.

Right click on the query in the database window (or navpane). Select the
Object Properties, and copy the description that I gave you into the
description. This is a great way to document what a query does. I use it
all the time when I'm creating complex queries.

Good luck.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Dale, thank you for your response. Your version is a very interesting way at
solving the problem I am facing! I was able to get your query to work and
return the numbers I need, however, I am having trouble wrapping my head
around the logic that is being used:

I understand that the subquery returns a list of every day in each
reservation and that the main SQL statement simply sums the days for each
month (which is pretty ingenious if you ask me) however it is not as
straightforward as I would like as I can picture myself coming back to this
in a few months and trying to figure out what I was doing...that being said,
I am keeping that as a viable option but I would also like to see if I can
figure out another method.

Thanks again for your help!
Chad

Dale Fye said:
Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
:

Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

:

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

What did the final query look like using my example? Since it appears you
are actually looking for a total reservation days, across multiple years and
months, rather than for a particular reservation, as you example implied.

How long is your longest reservation? If it is no more than 99 days, you
could modify qry_Numbers by getting rid of the Hundreds table. That would
give you 90% fewer records in the subquerys initial result set.

You might also be able to speed it up some by changing the WHERE clause to:

WHERE intNumber <= DateDiff("d", [ArrivalDate], [DepartureDate])


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Dale,

After spending some time with it, I am feeling more comfortable with the
logic. The only problem I see with it is that when I run the query it is
taking up a lot of processing power and takes close to a minute to run

This is probably due to the number of reservations and the length of
stay...about 50,000 reservations at an average of about 5 days apiece, which
causes the subquery to return about 250,000 rows. Is there a way to make
this method more efficient?

If not, I was able to get my version to work correctly and it runs in less
than 5 seconds...in case it will help anyone, here is my SQL:

************************************************************
SELECT tblYears.Year_ID, tblMonths.Month_ID, tblMonths.Month, Nz((Select
SUM(([Departure Date] - [Arrival Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1))) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0) +

Nz((Select Sum(Day([Departure Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1,
1)-1)) As Days From tblReservations WHERE (Month([Departure Date])>Month_ID
AND Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)),0) AS Days_Reserved

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

Dale, thanks again for all of your help in this matter and I will definitely
be using the method you provided me with in many other situations.

Thanks!

Chad
Dale Fye said:
I think you will find the method I provided is the simplest method. I use my
numbers table and query quite frequently when I'm working with dates. There
are a wide variety of other uses for it as well.

Right click on the query in the database window (or navpane). Select the
Object Properties, and copy the description that I gave you into the
description. This is a great way to document what a query does. I use it
all the time when I'm creating complex queries.

Good luck.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Dale, thank you for your response. Your version is a very interesting way at
solving the problem I am facing! I was able to get your query to work and
return the numbers I need, however, I am having trouble wrapping my head
around the logic that is being used:

I understand that the subquery returns a list of every day in each
reservation and that the main SQL statement simply sums the days for each
month (which is pretty ingenious if you ask me) however it is not as
straightforward as I would like as I can picture myself coming back to this
in a few months and trying to figure out what I was doing...that being said,
I am keeping that as a viable option but I would also like to see if I can
figure out another method.

Thanks again for your help!
Chad

:

Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
:

Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

:

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
C

Chad

My final query was:

***********************************************************
SELECT
Year(Temp.ResDate) AS Year_ID,
Month(Temp.ResDate) AS Month_ID,
Format(Temp.ResDate,"mmm") AS [Month],
Count(Temp.[Reservation ID]) AS Days
FROM
(SELECT [Reservation ID], DateAdd("d",intNumber,[Arrival Date]) AS
ResDate FROM tblReservations, qryInteger WHERE DateAdd("d",intNumber,[Arrival
Date])<[Departure Date]) AS Temp

GROUP BY Year(Temp.ResDate), Month(Temp.ResDate), Format(Temp.ResDate,"mmm");

************************************************************
I deleted to 'Hundreds' calc and it greatly improved the efficiency (brought
the run time to 8 - 10 seconds).

Thanks again for all of your help.

Chad

Dale Fye said:
What did the final query look like using my example? Since it appears you
are actually looking for a total reservation days, across multiple years and
months, rather than for a particular reservation, as you example implied.

How long is your longest reservation? If it is no more than 99 days, you
could modify qry_Numbers by getting rid of the Hundreds table. That would
give you 90% fewer records in the subquerys initial result set.

You might also be able to speed it up some by changing the WHERE clause to:

WHERE intNumber <= DateDiff("d", [ArrivalDate], [DepartureDate])


--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Chad said:
Dale,

After spending some time with it, I am feeling more comfortable with the
logic. The only problem I see with it is that when I run the query it is
taking up a lot of processing power and takes close to a minute to run

This is probably due to the number of reservations and the length of
stay...about 50,000 reservations at an average of about 5 days apiece, which
causes the subquery to return about 250,000 rows. Is there a way to make
this method more efficient?

If not, I was able to get my version to work correctly and it runs in less
than 5 seconds...in case it will help anyone, here is my SQL:

************************************************************
SELECT tblYears.Year_ID, tblMonths.Month_ID, tblMonths.Month, Nz((Select
SUM(([Departure Date] - [Arrival Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1))) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0) +

Nz((Select Sum(Day([Departure Date])) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)),0)+

Nz((Select SUM(DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1,
1)-1)) As Days From tblReservations WHERE (Month([Departure Date])>Month_ID
AND Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)),0) AS Days_Reserved

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

Dale, thanks again for all of your help in this matter and I will definitely
be using the method you provided me with in many other situations.

Thanks!

Chad
Dale Fye said:
I think you will find the method I provided is the simplest method. I use my
numbers table and query quite frequently when I'm working with dates. There
are a wide variety of other uses for it as well.

Right click on the query in the database window (or navpane). Select the
Object Properties, and copy the description that I gave you into the
description. This is a great way to document what a query does. I use it
all the time when I'm creating complex queries.

Good luck.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Dale, thank you for your response. Your version is a very interesting way at
solving the problem I am facing! I was able to get your query to work and
return the numbers I need, however, I am having trouble wrapping my head
around the logic that is being used:

I understand that the subquery returns a list of every day in each
reservation and that the main SQL statement simply sums the days for each
month (which is pretty ingenious if you ask me) however it is not as
straightforward as I would like as I can picture myself coming back to this
in a few months and trying to figure out what I was doing...that being said,
I am keeping that as a viable option but I would also like to see if I can
figure out another method.

Thanks again for your help!
Chad

:

Chad,

Take a look at my recommendation. It is much simpler!!!

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Thanks for everyone's input...I was not able to get online last night and
made some pretty good progress with a series of subqueries. Clifford, I also
added a tblYears which serves the same purpose as you suggested...currently
my SQL looks like:

************************************************************

SELECT
tblYears.Year_ID,

tblMonths.Month_ID,

tblMonths.Month,

(Select ([Departure Date] - [Arrival Date]) As Days From tblReservations
WHERE (Month([Departure Date])=Month_ID AND Month([Arrival Date])=Month_ID)
AND (Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
SameMo,

(Select DateDiff("d", DateSerial(2006,Month_ID,
1),DateSerial(2006,Month_ID+1, 1)) As Days From tblReservations WHERE
(Month([Departure Date])>Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
WholeMo,

(Select Day([Departure Date]) As Days From tblReservations WHERE
(Month([Departure Date])=Month_ID AND Month([Arrival Date])<Month_ID) AND
(Year([Departure Date])=Year_ID AND Year([Arrival Date])=Year_ID)) AS
FutureMo,

(Select DateDiff("d",[Arrival Date],DateSerial(Year_ID, Month_ID+1, 1)-1) As
Days From tblReservations WHERE (Month([Departure Date])>Month_ID AND
Month([Arrival Date])=Month_ID)AND (Year([Departure Date])=Year_ID AND
Year([Arrival Date])=Year_ID)) AS PreviousMo

FROM tblMonths, tblYears
ORDER BY tblYears.Year_ID, tblMonths.Month_ID;

************************************************************

This query returns the data that I need but in 4 different columns. I tried
adding the subqueties together to combine the total days into 1 column but
when I attempted this, it returned null values for every period.

Is there a way I can combine this data into one column or should I create a
second query to do this?

Clifford, I haven't tried your method yet, if I can't get mine to work I
will play around with it.

Thanks again for everyone's help.

Thanks,
Chad
:

Hi Chad,

I liked your question enough to putter around with it! So ignore my
prior post. Try this, which will deal with any span of time. Create a table
tblYear with one field Calendar_Year, an integer and the primary index.
Populate the table with all the years you need and more into the future to
cover future information. So if your data starts with 2006, you would do
2006, 2007 and so on until 2015 or something like that. Create a query named
"qryYears Months Start End". Its SQL will be:

SELECT Y.Calendar_Year, M.Month_ID, M.Month_Desc,
DateSerial([Calendar_Year],[Month_ID],1) AS Current_Month_Start,
DateAdd("d",-1,DateAdd("m",1,[Current_Month_Start])) AS Current_Month_End
FROM tblYear AS Y, tblMonth AS M
WHERE (((Y.Calendar_Year) Between (select Year(Min(Arrival_Date)) from
tblReservation) And (select Year(Max(Departure_Date)) from tblReservation)));

Create another query, named whatever you want. Its SQL will be:

SELECT YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc,
Sum(Switch([Arrival_Date]>=[Current_Month_Start] And
[Departure_Date]<=[Current_Month_End],DateDiff("d",[Arrival_Date],[Departure_Date]),[Current_Month_Start]>=[Arrival_Date]
And
[Current_Month_End]<=[Departure_Date],DateDiff("d",[Current_Month_Start],[Current_Month_End]),[Arrival_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Arrival_Date],[Current_Month_End]),[Departure_Date]
Between [Current_Month_Start] And
[Current_Month_End],DateDiff("d",[Current_Month_Start],[Departure_Date]),True,-1)+1) AS Day_Count
FROM [qryYears Months Start End] AS YMSE, tblReservation AS R
GROUP BY YMSE.Calendar_Year, YMSE.Month_ID, YMSE.Month_Desc;

The results will be all of the months in each calendar year, with their
repective counts. Probably the contents of the Switch() function could be
simplified. I leave that to those who wish to attempt it.

Clifford Bass

:

Hello everyone. I have beed struggling with this for a while but can't seem
to figure it out. I have a database that has two tables with the associated
fields:

tblReservation
- Reservation_ID
- Arrival_Date
- Departure_Date

tblMonth
- Month_ID (1, 2, etc.)
- Month_Desc (Jan, Feb, etc.)


I am trying to develop a query that lists all twelve months and the number
of days associated with that month based on the Arrival and Departure Dates.

For Example:

If a reservation spans from 1/27/08 - 2/3/2008, I would wan tthe query to
return:

Month Days
Jan 5
Feb 3
etc...

I am able to return the correct numbers if the reservation doesn't span more
than one month, but I am not sure of the best way to return the data as
above. Any help would be greatly appreciated.

Thanks!
Chad
Click to expand...
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Chad,

You may want to check your results. Are they giving you the correct
numbers or are there days missing. If there are days missing, as I suspect,
change your [Arrival Date])<[Departure Date] to [Arrival Date])<=[Departure
Date].

Clifford Bass
 
D

Dale Fye

When counting occupancy, departure date does not normally count.

Dale

Clifford Bass said:
Hi Chad,

You may want to check your results. Are they giving you the correct
numbers or are there days missing. If there are days missing, as I
suspect,
change your [Arrival Date])<[Departure Date] to [Arrival
Date])<=[Departure
Date].

Clifford Bass

Chad said:
My final query was:

***********************************************************
SELECT
Year(Temp.ResDate) AS Year_ID,
Month(Temp.ResDate) AS Month_ID,
Format(Temp.ResDate,"mmm") AS [Month],
Count(Temp.[Reservation ID]) AS Days
FROM
(SELECT [Reservation ID], DateAdd("d",intNumber,[Arrival Date])
AS
ResDate FROM tblReservations, qryInteger WHERE
DateAdd("d",intNumber,[Arrival
Date])<[Departure Date]) AS Temp

GROUP BY Year(Temp.ResDate), Month(Temp.ResDate),
Format(Temp.ResDate,"mmm");

************************************************************
I deleted to 'Hundreds' calc and it greatly improved the efficiency
(brought
the run time to 8 - 10 seconds).

Thanks again for all of your help.

Chad
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Dale,

I wondered. However, his original example counted the departure date
and that was what I was basing my comment on.

Also, I have been playing around some with your ideas, wondering why he
initially got bogged down when using the 1000-day timeframe. It could be an
issue for those that need to count timeframes greater than 99. And, I have
been playing around with my solution to see if there is a better option in
that direction. I will be posting some results/thinking later on.

I do like your numbers table/query thingy! Pretty neat! I have added
it to my repetoir of tricks.

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Date Range 1
Date Range 7
work out days in each month 3
Remove Data Outliers 1
CrossTAb Issue 3
Getting total per month 2
Determining EVERY month between 2 given dates 13
CALCULATE DAYS PER MONTH BETWEEN 2 DATES 2

Top