sql - 创建一个字段来计算日期范围内一个月内的天数？

Question

 Similar to the following:

Count days within a month from date range

我想在 MS-Access 查询设计环境中找到一种方法来创建用于计算日期范围内月/年天数的字段。

这是我想要的数据:

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

请记住，月份和年份都很重要，因为我需要能够区分 2010 年 1 月和 2011 年 1 月给定日期范围内的天数，而不是仅仅区分给定日期范围内的天数一月的给定日期范围。

如果有一种系统的方法可以通过在 Access 中使用 SQL 来执行创建这些字段，那将是我的首选方法。

但是，如果不可能(或非常困难)这样做，我想知道如何在表达式构建器中构建每个字段，这样我至少可以生成一个计数字段一次。

一如既往，非常感谢您抽出宝贵时间。

Answer 1

在某些情况下，“日期表”可以帮助进行日期操作。类似于“数字表”，“日期表”是一个包含给定范围内每个日期一行的表格，通常涵盖实际数据中预期会遇到的整个日期范围。

对于名为 [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

还有一个简单的[DatesTable]

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

查询

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

为每个 [SampleData].[Row] 值的间隔中的每个日期返回一行。 (对于这个特定的示例数据，总共有 1568 行。)

对其进行聚合

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

给我们所有的计数

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

然后我们可以就此进行报告，或对其进行交叉制表，或做任何其他有趣的事情。

旁注:

“日期表”非常有用的一种情况是我们必须处理法定假期。那是因为

1. 有时法定假日的“休息日”并非实际日期。如果“国际培根日”是星期日，那么我们可能会在星期一休息。

2. 一些法定假期可能很难计算。例如，耶稣受难日对我们加拿大人来说是(如果我没记错的话)“春分后第一个满月后的第一个星期日之前的星期五”。

如果我们有一个“日期表”，那么我们可以添加一个 [StatutoryHoliday] Yes/No 字段来标记所有(观察到的)假期，然后使用 ... WHERE NOT StatutoryHoliday 以排除它们。