按周数访问SQL人数组

Question

给定一周的工作量，我需要计算有多少人在工作。

这是我的人员表（日期为美国格式）：

name | surname| date_of_entry | date_of_exit
-----|--------|---------------|-------------
foo  | bar    | 1/1/2006      | 1/8/2006
foo1 | bar1   | 1/5/2010      |
foo2 | bar2   | 2/3/2015      | 3/4/2015

我希望在给定的一年中，所有星期数都与在此期间工作的适当人数保持一致。
希望您能理解我，因为英语不是我的母语。

我已经进行了一些研究，根据我的理解，根据以上示例，我需要创建一张表，从2006年1月1日开始，到“现在”结束（因为没有退出日期的人仍在工作）。能够测试每个人是否在本周内在工作，因此我可以在查询中算出他。

我仍然是一名学生程序员，但对我来说这似乎是一个非常复杂的SQL查询。

2006年查询的预期输出（新年从星期一开始）：

week_number | count
------------|------
   1        | 1
   2        | 1
   3        | 0
   etc..    | 0

直到1/5/2010，其中所有星期在“计数”字段中都有1，然后 查询2015年：

week_number | count
------------|------
   1        | 1
   2        | 1
   ...      | 1
   9        | 2
   ...      | 2
   14       | 1
   ...      | 1

如果有人可以帮助我解决这个问题，那就太好了，谢谢！

Answer 1

您很幸运，我不得不改变主意15分钟。

第1步：创建日历表

创建一个Calendar具有以下字段的新表

• id ：自动编号
• Cal_Year ： 数字
• Cal_Week ： 数字

在一个模块中，添加以下代码并执行它（F5）：

Private Sub Create_Calendar_table()

    Dim Y As Integer
    Dim W As Integer

    For Y = 2006 To 2016
        For W = 1 To 52
            DoCmd.RunSQL "INSERT INTO Calendar (cal_year, cal_week) VALUES (" & Y & "," & W & ")"
        Next W
    Next Y

End Sub

Calendar 该表现在可以使用了：

ID  Cal_year    Cal_week
1   2006        1
2   2006        2
3   2006        3
4   2006        4
5   2006        5
    and so on...

步骤2：建立查询

请注意，我在欧洲，所以我的日期是DD / MM。 这不会影响您的结果。

我会分解，以便您了解该过程。

首先，我们需要从日历表中的年/周创建一个日期，可以像这样实现

SELECT Cal_year, Cal_week, DateAdd("ww",Cal_week,DateSerial(Cal_year,1,1)) AS thedate
FROM Calendar

Cal_year    Cal_week    thedate
2006            1       8/01/2006
2006            2       15/01/2006
2006            3       22/01/2006
2006            4       29/01/2006
2006            5       5/02/2006   
and so on...

接下来，由于我们将处理日期范围，因此重要的是，当人们的exit_date为时，将 当前日期 归属为NULL，例如：

nz(date_of_exit, Now)

字段已准备就绪。

然后，把戏。

我们需要将JOIN日历表与人员表一起使用，以这种方式将返回每个人在场的记录。

实现这一目标的关键是 ON...BETWEEN...AND

SELECT C.Cal_year, C.Cal_Week, P.pname, P.psurname, P.date_of_entry, nz(P.date_of_exit, Now) AS exit_date
FROM [Calendar] C
INNER JOIN ( SELECT [Name] AS pname, [surname] as psurname, date_of_entry, date_of_exit
            FROM people
           ) P  
ON (DateAdd("ww",C.Cal_week,DateSerial(C.Cal_year,1,1)) BETWEEN P.date_of_entry  AND nz(P.date_of_exit, Now))
ORDER BY  C.Cal_year, C.Cal_Week

Cal_year    Cal_Week pname      psurname    date_of_entry       exit_date
2006            1   foo         bar         1/01/2006               8/01/2006
2010            1   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            2   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            3   foo1        bar1        5/01/2010               22/04/2016 13:04:39
2010            4   foo1        bar1        5/01/2010               22/04/2016 13:04:39

请注意，如果您需要自2006年以来的所有周，即使没有周，也INNER JOIN可以使用LEFT JOIN

最后，我们利用上一个查询通过GROUP BY对日历表的年和周进行计数来计算存在
，并在WHERE子句中指定2015年，否则它将对2006年以来的所有事件进行计数。这意味着计数非常容易一年的存在。

SELECT yyyy, ww , count(*) AS cnt
FROM
(         
    SELECT C.Cal_year AS yyyy, C.Cal_Week AS ww
    FROM [Calendar] C
    INNER JOIN ( SELECT [Name] AS pname, [surname] as psurname, 
                       date_of_entry,  
                       date_of_exit
                FROM people
               ) P  ON (DateAdd("ww",C.Cal_week,DateSerial(C.Cal_year,1,1)) BETWEEN P.date_of_entry  AND nz(P.date_of_exit, Now))
)
WHERE yyyy=2015
GROUP BY yyyy, ww
ORDER BY yyyy, ww

yyyy    ww  cnt
2015    1   1
2015    2   1
2015    3   1
2015    4   1
2015    5   1
2015    6   1
2015    7   1
2015    8   1
2015    9   2
2015    10  2
2015    11  2
2015    12  2
2015    13  2
2015    14  1
2015    15  1
2015    16  1

好吧，我终于花了40分钟…