Обчислити робочий час щомісяця. MS Access

I need to calculate the total working time from a single table. My problem is the working time sometimes elapse over the month end and I need the working hours for each month. Here is some example data:

  Персонал StartTime EndTime
3/31 16:50 3/31 19:30 B
3/31 23:10 4/1 1:30 B
4/1 1:40 4/1 03:55 B
 

The result should be

Month   Staff      WorkingHours
March     B            3:30
April     B            1:30

Спасибі заздалегідь

0

6 Відповіді

У цій проблемі є два види записів:

  1. записи, в яких [StartTime] і [StopTime] знаходяться в один і той же місяць, а

  2. записи, де вони відсутні.

Розрахунок часу, що минув для першого випадку, простий:

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])=Month([StartTime])

Обчислення часу, що минув для записів, що охоплюють місяць, є двочастинним процесом:

(і) Отримати минулий час для попереднього місяця

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

(ii) Отримайте минулий час на наступний місяць

SELECT 
    Staff, 
    Month([EndTime]) AS WorkMonth,
    DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

Таким чином, щоб отримати підсумки, ми просто СОЮЗІ ВСІ три запити і загорнути їх в запит агрегування для виконання SUM ()

SELECT WorkMonth, Staff, SUM(MinutesWorked) AS TotalMinutes
FROM
    (
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])=Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([EndTime]) AS WorkMonth,
            DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
    )
GROUP BY WorkMonth, Staff

Це повернеться ...

WorkMonth  Staff  TotalMinutes
---------  -----  ------------
        3  B               210
        4  B               225

... і ви можете додати функції Format() , якщо ви хочете налаштувати форматування місяця (наприклад, як текст) або загальне час (наприклад, як "hh: nn").

1
додано
+1 тому, що використання SQL завжди краще, ніж перейти через набори записів.
додано Автор Clon, джерело
Дуже дякую. Це відмінно вирішить мою проблему.
додано Автор user2515536, джерело

У цій проблемі є два види записів:

  1. записи, в яких [StartTime] і [StopTime] знаходяться в один і той же місяць, а

  2. записи, де вони відсутні.

Розрахунок часу, що минув для першого випадку, простий:

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])=Month([StartTime])

Обчислення часу, що минув для записів, що охоплюють місяць, є двочастинним процесом:

(і) Отримати минулий час для попереднього місяця

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

(ii) Отримайте минулий час на наступний місяць

SELECT 
    Staff, 
    Month([EndTime]) AS WorkMonth,
    DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

Таким чином, щоб отримати підсумки, ми просто СОЮЗІ ВСІ три запити і загорнути їх в запит агрегування для виконання SUM ()

SELECT WorkMonth, Staff, SUM(MinutesWorked) AS TotalMinutes
FROM
    (
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])=Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([EndTime]) AS WorkMonth,
            DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
    )
GROUP BY WorkMonth, Staff

Це повернеться ...

WorkMonth  Staff  TotalMinutes
---------  -----  ------------
        3  B               210
        4  B               225

... і ви можете додати функції Format() , якщо ви хочете налаштувати форматування місяця (наприклад, як текст) або загальне час (наприклад, як "hh: nn").

1
додано
+1 тому, що використання SQL завжди краще, ніж перейти через набори записів.
додано Автор Clon, джерело
Дуже дякую. Це відмінно вирішить мою проблему.
додано Автор user2515536, джерело

У цій проблемі є два види записів:

  1. записи, в яких [StartTime] і [StopTime] знаходяться в один і той же місяць, а

  2. записи, де вони відсутні.

Розрахунок часу, що минув для першого випадку, простий:

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])=Month([StartTime])

Обчислення часу, що минув для записів, що охоплюють місяць, є двочастинним процесом:

(і) Отримати минулий час для попереднього місяця

SELECT 
    Staff, 
    Month([StartTime]) AS WorkMonth,
    DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

(ii) Отримайте минулий час на наступний місяць

SELECT 
    Staff, 
    Month([EndTime]) AS WorkMonth,
    DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
FROM WorkLog
WHERE Month([EndTime])>Month([StartTime])

Таким чином, щоб отримати підсумки, ми просто СОЮЗІ ВСІ три запити і загорнути їх в запит агрегування для виконання SUM ()

SELECT WorkMonth, Staff, SUM(MinutesWorked) AS TotalMinutes
FROM
    (
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])=Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([StartTime]) AS WorkMonth,
            DateDiff("n", [StartTime], DateSerial(Year([StartTime]), Month([StartTime]) + 1, 1)) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
        UNION ALL
        SELECT 
            Staff, 
            Month([EndTime]) AS WorkMonth,
            DateDiff("n", DateSerial(Year([EndTime]), Month([EndTime]), 1), [EndTime]) AS MinutesWorked
        FROM WorkLog
        WHERE Month([EndTime])>Month([StartTime])
    )
GROUP BY WorkMonth, Staff

Це повернеться ...

WorkMonth  Staff  TotalMinutes
---------  -----  ------------
        3  B               210
        4  B               225

... і ви можете додати функції Format() , якщо ви хочете налаштувати форматування місяця (наприклад, як текст) або загальне час (наприклад, як "hh: nn").

1
додано
+1 тому, що використання SQL завжди краще, ніж перейти через набори записів.
додано Автор Clon, джерело
Дуже дякую. Це відмінно вирішить мою проблему.
додано Автор user2515536, джерело

Можливо, ви захочете ознайомитись з логічним твердженням за допомогою datediff() звідси . Це дозволяє легко відняти місяці, дні, години тощо.

Якщо вам слід додати в початковий запит If-Else :

If DateDiff("m", #Date1#, #Date2#) = 0 Then

    'The times here would be in the same month

Else

    `The times here would split months

End If

І якщо ви не хочете використовувати логічне твердження в VBA, ви також можете запустити запит DateDiff ().

Я не знаю, скільки записів ви маєте тут справу, так що цикл по кожній запису, якщо у вас є 10-ти з 1000, може бути не найефективнішим, але це допоможе виконати роботу. Якщо ви не знайомі з підходом VBA, я був би радий змінити існуючий If-Else з трохи більше даних, щоб вказувати вас у правильному напрямку.

0
додано

Можливо, ви захочете ознайомитись з логічним твердженням за допомогою datediff() звідси . Це дозволяє легко відняти місяці, дні, години тощо.

Якщо вам слід додати в початковий запит If-Else :

If DateDiff("m", #Date1#, #Date2#) = 0 Then

    'The times here would be in the same month

Else

    `The times here would split months

End If

І якщо ви не хочете використовувати логічне твердження в VBA, ви також можете запустити запит DateDiff ().

Я не знаю, скільки записів ви маєте тут справу, так що цикл по кожній запису, якщо у вас є 10-ти з 1000, може бути не найефективнішим, але це допоможе виконати роботу. Якщо ви не знайомі з підходом VBA, я був би радий змінити існуючий If-Else з трохи більше даних, щоб вказувати вас у правильному напрямку.

0
додано

Можливо, ви захочете ознайомитись з логічним твердженням за допомогою datediff() звідси . Це дозволяє легко відняти місяці, дні, години тощо.

Якщо вам слід додати в початковий запит If-Else :

If DateDiff("m", #Date1#, #Date2#) = 0 Then

    'The times here would be in the same month

Else

    `The times here would split months

End If

І якщо ви не хочете використовувати логічне твердження в VBA, ви також можете запустити запит DateDiff ().

Я не знаю, скільки записів ви маєте тут справу, так що цикл по кожній запису, якщо у вас є 10-ти з 1000, може бути не найефективнішим, але це допоможе виконати роботу. Якщо ви не знайомі з підходом VBA, я був би радий змінити існуючий If-Else з трохи більше даних, щоб вказувати вас у правильному напрямку.

0
додано