FromDate - ToDate

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

I have a table that contains a FromDate field and a ToDate field. Can anyone
help me determine the best way to develope a query (or set of quesries) that
will allow me to return the number of days, by month, that is included in
this table?

Thanks in advance,
Chad
 
I can't wrap my head around what you are asking especially the "by month"
part. What happens if the FromDate and ToDate are in different months?

Maybe you could provide some sample data, includign the proper table and
field names, and the data you wished returned.
 
I have a table that contains a FromDate field and a ToDate field. Can anyone
help me determine the best way to develope a query (or set of quesries) that
will allow me to return the number of days, by month, that is included in
this table?

Thanks in advance,
Chad
Click to expand...

What do you mean by "number of days, by month"? the number of days in January,
the number of days in February, and then the number of days in March as three
separate values, or what?


John W. Vinson [MVP]
 
sorry for the confusion everyone, below are a few more specifics.

I have a table called tblAvailability with three fields:

Property_ID
From_Date
To_Date

The from and two dates actually specify which days the property is avaiable.
FOr example:

Property_ID From_Date To_Date
Property1 1/15/2007 3/2/2007

This record specifies that Property1 is available from 1/15/2007 trhough
3/2/2007.

I would like to run a query that would group by property, by month, and sum
the number of days that property has available each month. In ths example,
the query would return:

Property_ID Month No_Days
Property1 January 17
Property1 February 28
Property1 March 2

Thanks again for any help you can provide.

Chad
 
sorry for the confusion everyone, below are a few more specifics.

I have a table called tblAvailability with three fields:

Property_ID
From_Date
To_Date

The from and two dates actually specify which days the property is avaiable.
FOr example:

Property_ID From_Date To_Date
Property1 1/15/2007 3/2/2007

This record specifies that Property1 is available from 1/15/2007 trhough
3/2/2007.

I would like to run a query that would group by property, by month, and sum
the number of days that property has available each month. In ths example,
the query would return:

Property_ID Month No_Days
Property1 January 17
Property1 February 28
Property1 March 2
Click to expand...

This will be easiest if you create a little auxiliary table AllDates with one
date field, with one record per day covering the range of dates the
application needs to cover. A full decade is only 3654 or so rows - tiny - and
can be filled by using Excel's fill-series feature and copying and pasting the
column into a table. Let's call this field TheDate (don't use the reserved
word Date as a fieldname).

A query

SELECT tblAvailability.PropertyID, Format(TheDate, "yyyy mmmm") AS TheMonth,
Count(*) AS No_Days
FROM tblAvailability INNER JOIN AllDates
On TheDate BETWEEN tblAvailability.From_Date AND tblAvailability.ToDate
GROUP BY PropertyID, Year(TheDate), Month(TheDate), Format(TheDate, "yyyy
mmmm");

should give you the result (and let you handle the range November 15 through
February 10 as well).

John W. Vinson [MVP]
 
Back
Top