MS Access 查询，如何使用 SQL 将单个日期分组为周 [英] MS Access query, how to use SQL to group single dates into weeks

问题描述

我目前有两张桌子.一个有员工姓名和一个与该姓名相关联的号码.

I currently have two tables. One has Employee Names and a number associated to that name.

另一个日期有时间表日期，其中包含员工编号、日期和该日期的工作小时数列.

The other date has time sheet date with columns for the employee number, the date and the number of hours worked on that date.

我想创建一个交叉表查询，显示一列中的员工姓名，每列中显示一周结束的日期，然后显示特定员工该周的小时数总和.

I want to create a cross tab query that shows the employee names in one column with the date for the end of the week in each column, then show to sum of hours for that week for a particular employee.

我当前的查询有效，但只能按月分组.我很难弄清楚如何将几天分成几周.

My current query works but only groups by month. I am struggle to work out how to group days into weeks.

TRANSFORM Sum(tblTimeSheetData.WorkHours) AS SumOfHours
SELECT tblEmployees.Combined
FROM tblTimeSheetData RIGHT JOIN tblEmployees ON tblTimeSheetData.EmployeeID = 
tblEmployees.EmployeeID
GROUP BY tblEmployees.Combined
ORDER BY tblEmployees.Combined, Format([WorkDate],"yyyy-mm")
PIVOT Format([WorkDate],"yyyy-mm");

推荐答案

由于第一个和最后一周的数字跨日历年，因此必须包括年份和周数:

As the first and last week numbers cross calendar years, both year and week number must be included:

Option Compare Database
Option Explicit

    Public Const MaxWeekValue           As Integer = 53
    Public Const MinWeekValue           As Integer = 1
    Public Const MaxMonthValue          As Integer = 12
    Public Const MinMonthValue          As Integer = 1

' Returns, for a date value, a formatted string expression with
' year and weeknumber according to ISO-8601.
' Optionally, a W is used as separator between the year and week parts.
'
' Typical usage:
'
'   FormatWeekIso8601(Date)
'   ->  2017-23
'
'   FormatWeekIso8601(Date, True)
'   ->  2017W23
'
' 2017-04-28. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatWeekIso8601( _
    ByVal Expression As Variant, _
    Optional ByVal WeekSeparator As Boolean) _
    As String

    Const Iso8601Separator  As String = "W"
    Const NeutralSeparator  As String = "-"

    Dim Result              As String

    Dim IsoYear As Integer
    Dim IsoWeek As Integer

    If IsDate(Expression) Then
        IsoWeek = Week(DateValue(Expression), IsoYear)
        Result = _
            VBA.Format(IsoYear, String(3, "0")) & _
            IIf(WeekSeparator, Iso8601Separator, NeutralSeparator) & _
            VBA.Format(IsoWeek, String(2, "0"))
    End If

    FormatWeekIso8601 = Result

End Function

' Returns the ISO 8601 week of a date.
' The related ISO year is returned by ref.
'
' 2016-01-06. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function Week( _
    ByVal Date1 As Date, _
    Optional ByRef IsoYear As Integer) _
    As Integer

    Dim Month       As Integer
    Dim Interval    As String
    Dim Result      As Integer

    Interval = "ww"

    Month = VBA.Month(Date1)
    ' Initially, set the ISO year to the calendar year.
    IsoYear = VBA.Year(Date1)

    Result = DatePart(Interval, Date1, vbMonday, vbFirstFourDays)
    If Result = MaxWeekValue Then
        If DatePart(Interval, DateAdd(Interval, 1, Date1), vbMonday, vbFirstFourDays) = MinWeekValue Then
            ' OK. The next week is the first week of the following year.
        Else
            ' This is really the first week of the next ISO year.
            ' Correct for DatePart bug.
            Result = MinWeekValue
        End If
    End If

    ' Adjust year where week number belongs to next or previous year.
    If Month = MinMonthValue Then
        If Result >= MaxWeekValue - 1 Then
            ' This is an early date of January belonging to the last week of the previous ISO year.
            IsoYear = IsoYear - 1
        End If
    ElseIf Month = MaxMonthValue Then
        If Result = MinWeekValue Then
            ' This is a late date of December belonging to the first week of the next ISO year.
            IsoYear = IsoYear + 1
        End If
    End If

    ' IsoYear is returned by reference.
    Week = Result

End Function

如果您只想以最后一周的日期为中心，您可以使用以下表达式:

If you just want to pivot on the ultimo week date, you can use this expression:

DateAdd("d", 7 - Weekday([WorkDate], vbMonday), [WorkDate])

这假定 ISO 周编号，其中星期一是一周的第一天.

This assumes ISO week numbering where Monday is the first day of the week.

这篇关于MS Access 查询，如何使用 SQL 将单个日期分组为周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！