CODEWITHSUNDEEP
sqldatabasedatems-accesscount
ealfons1
ealfons1

Reputation: 373

Creating a field(s) that counts days within a month from date range?

 Similar to the following:

Count days within a month from date range

I want to find a way, within the MS-Access Query Design environment, to create fields that count the number of month/year days within a date range.

Here is what I want the data to look like:

Row |  StartDate | EndDate |  #DaysJan2010 | #DaysFeb2010 |  #DaysMarch2010

001    01/02/2010 02/04/2012        29             28              31
002    01/02/2010 01/05/2010         4              0               0
003    04/02/2010 05/05/2010         0              0               0     
004    01/02/2010 02/04/2012        29             28              31
005    02/02/2012 02/03/2012         0              2               0

Please keep in mind that both month and year are important because I need to be able to distinguish between the number of days that fall within a given date range for January 2010 and January 2011, as opposed to just the number of days within a given date range that are in January.

If there is a systematic way of performing of creating these fields by using SQL in Access, that would be my preferred method.

However, in the event that it is impossible (or very difficult) to do so, I would like to know how to build each field in the expression builder, so that I may at least be able to generate the count fields one at a time.

As always, thank you very much for your time.

Upvotes: 2

Views: 659

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123409

There are cases where date manipulations can be aided by a "dates table". Similar to a "numbers table", a "dates table" is a table containing one row for every date in a given range, usually covering the entire range of dates that one could expect to encounter in the actual data.

For sample data in a table named [SampleData]

Row  StartDate   EndDate   
---  ----------  ----------
001  2010-01-02  2012-02-04
002  2010-01-02  2010-01-05
003  2010-04-02  2010-05-05
004  2010-01-02  2012-02-04
005  2012-02-02  2012-02-03

and a [DatesTable] that is simply

theDate
----------
2010-01-01
2010-01-02
2010-01-03
...
2012-12-30
2012-12-31

the query

SELECT
    sd.Row,
    dt.theDate,
    Year(dt.theDate) AS theYear,
    Month(dt.theDate) AS theMonth
FROM
    SampleData AS sd
    INNER JOIN
    DatesTable AS dt
        ON dt.theDate >= sd.StartDate
            AND dt.theDate <= sd.EndDate

returns a row for each date in the interval for each [SampleData].[Row] value. (For this particular sample data, that's 1568 rows in total.)

Performing an aggregation on that

SELECT
    Row,
    theYear,
    theMonth,
    COUNT(*) AS NumberOfDays
FROM
    (
        SELECT
            sd.Row,
            dt.theDate,
            Year(dt.theDate) AS theYear,
            Month(dt.theDate) AS theMonth
        FROM
            SampleData AS sd
            INNER JOIN
            DatesTable AS dt
                ON dt.theDate >= sd.StartDate
                    AND dt.theDate <= sd.EndDate
    ) AS allDates
GROUP BY
    Row,
    theYear,
    theMonth

gives us all of the counts

Row  theYear  theMonth  NumberOfDays
---  -------  --------  ------------
001     2010         1            30
001     2010         2            28
001     2010         3            31
001     2010         4            30
001     2010         5            31
001     2010         6            30
001     2010         7            31
001     2010         8            31
001     2010         9            30
001     2010        10            31
001     2010        11            30
001     2010        12            31
001     2011         1            31
001     2011         2            28
001     2011         3            31
001     2011         4            30
001     2011         5            31
001     2011         6            30
001     2011         7            31
001     2011         8            31
001     2011         9            30
001     2011        10            31
001     2011        11            30
001     2011        12            31
001     2012         1            31
001     2012         2             4
002     2010         1             4
003     2010         4            29
003     2010         5             5
004     2010         1            30
004     2010         2            28
004     2010         3            31
004     2010         4            30
004     2010         5            31
004     2010         6            30
004     2010         7            31
004     2010         8            31
004     2010         9            30
004     2010        10            31
004     2010        11            30
004     2010        12            31
004     2011         1            31
004     2011         2            28
004     2011         3            31
004     2011         4            30
004     2011         5            31
004     2011         6            30
004     2011         7            31
004     2011         8            31
004     2011         9            30
004     2011        10            31
004     2011        11            30
004     2011        12            31
004     2012         1            31
004     2012         2             4
005     2012         2             2

We can then report on that, or crosstab it, or do any number of other fun things.

Side note:

One circumstance where a "dates table" can be very useful is when we have to deal with Statutory Holidays. That is because

  1. Sometimes the "day off" for a Statutory Holiday is not the actual day. If "International Bacon Day" falls on a Sunday then we might get the Monday off.

  2. Some Statutory Holidays can be tricky to calculate. For example, Good Friday for us Canadians is (if I remember correctly) "the Friday before the first Sunday after the first full moon after the Spring Equinox".

If we have a "dates table" then we can add a [StatutoryHoliday] Yes/No field to flag all of the (observed) holidays and then use ... WHERE NOT StatutoryHoliday to exclude them.

Upvotes: 2

Related Questions