Sql TRANSFORM со временем расчета в MS Access

Я пытаюсь сделать Sql TRANSFORM со временем расчета в столбце и обратно.

Мне нужен ответ в 1 запросе, и я не хочу использовать эту функцию в доступе к MS, потому что я хочу использовать sql в vb.net

Пожалуйста, направь меня

Спасибо

Стол ABSEN

ДАТА ВРЕМЯ ВХОД СТАТУС РАСПОЛОЖЕНИЕ 26 августа 24 08:00:00 В ПОДАРОК БОДЖОНГ 26 августа 24 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 27-24 августа 08:00:00 В ПОДАРОК БОДЖОНГ 27-24 августа 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 28 августа 24 г. 08:00:00 В ПОДАРОК БОДЖОНГ 28 августа 24 г. 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 29-24 августа 08:00:00 В ПОДАРОК БОДЖОНГ 29-24 августа 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 30 августа 24 г. 08:00:00 В ПОДАРОК БОДЖОНГ 30 августа 24 г. 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 31 августа 24 г. 08:20:00 В ПОДАРОК БОДЖОНГ 31 августа 24 г. 17:00:00 ВНЕ ПОДАРОК БОДЖОНГ 01 сентября 24 г. 0 В НЕТ БОДЖОНГ 01 сентября 24 г. 0 ВНЕ НЕТ БОДЖОНГ

Стол MASTERDAYS

РАСПОЛОЖЕНИЕ ДЕНЬ РАЗМОРАЖИВАНИЕ ОПРЕДЕЛИТЬ БОДЖОНГ Понедельник 01:00:00 08:00:00 БОДЖОНГ Вторник 01:00:00 08:00:00 БОДЖОНГ Среда 01:00:00 08:00:00 БОДЖОНГ Четверг 01:00:00 08:00:00 БОДЖОНГ Пятница 01:30:00 08:00:00 БОДЖОНГ Суббота 01:00:00 08:00:00 БОДЖОНГ Воскресенье 00:00:00 00:00:00
TRANSFORM Max(ABSEN.Time) AS MaxOfTIME
SELECT ABSEN.Date AS [DATE], 
       Format(ABSEN.Date,'dddd') AS DAYS, 
       ABSEN.STATUS AS STATUS, 
       IIF(ABSEN.STATUS = 'NOT PRESENT', '',MASTERDAYS.DEFREST) AS DEFREST
FROM ABSEN INNER JOIN MASTERDAYS AS MASTERDAYS ON (MASTERDAYS.DAY = FORMAT(ABSEN.DATE,'dddd')) AND (MASTERDAYS.LOCATION = ABSEN.LOCATION)
GROUP BY ABSEN.Date, 
         Format(ABSEN.Date,'dddd'), 
         ABSEN.STATUS, MASTERDAYS.DEFREST
PIVOT ABSEN.INOUT In ('IN','OUT');

Результат из кода

ДАТА ДНИ СТАТУС РАЗМОРАЖИВАНИЕ В ВНЕ 26 августа 24 Понедельник ПОДАРОК 01:00:00 08:00:00 17:00:00 27-24 августа Вторник ПОДАРОК 01:00:00 08:00:00 17:00:00 28 августа 24 г. Среда ПОДАРОК 01:00:00 08:00:00 17:00:00 29-24 августа Четверг ПОДАРОК 01:00:00 08:00:00 17:00:00 30 августа 24 г. Пятница ПОДАРОК 01:30:00 08:00:00 17:00:00 31 августа 24 г. Суббота ПОДАРОК 01:30:00 08:00:00 17:00:00 01 сентября 24 г. Воскресенье НЕТ 0 0

Желаемый результат

DUROFWORK = (ABSEN.Time IN - ABSEN.Time OUT - MASTERDAYS.DEFREST) LATEIN = (ОТСУТСТВИЕ.ВРЕМЯ ВХ. - MASTERDAYS.DEFIN)

Для статуса NOT PRESENT хочу сделать заготовку для DEFREST,IN,OUT,DUROFWORK,LATEIN

ДАТА ДНИ СТАТУС РАЗМОРАЖИВАНИЕ В ВНЕ ДЮРОФВОРК ЛАТЕИН 26 августа 24 Понедельник ПОДАРОК 01:00:00 08:00:00 17:00:00 08:00:00 27-24 августа Вторник ПОДАРОК 01:00:00 08:00:00 17:00:00 08:00:00 28 августа 24 г. Среда ПОДАРОК 01:00:00 08:00:00 17:00:00 08:00:00 29-24 августа Четверг ПОДАРОК 01:00:00 08:00:00 17:00:00 08:00:00 30 августа 24 г. Пятница ПОДАРОК 01:30:00 08:00:00 17:00:00 07:30:00 31 августа 24 г. Суббота ПОДАРОК 01:30:00 08:00:00 17:00:00 06:10:00 00:20:00 01 сентября 24 г. Воскресенье НЕТ
Info data type 

Public Class DTOABSENTRANSFORMREPORT
    Public Property [DATE] As DateTime
    Public Property DAYS As String
    Public Property STATUS As String
    Public Property DEFREST As String
    Public Property TIME As String
    Public Property [IN] As String
    Public Property OUT As String
    Public Property DUROFWORK As String
    Public Property LATEIN As String
End Class

🤔 А знаете ли вы, что...
SQL поддерживает агрегирование данных с использованием операторов GROUP BY и HAVING.


1
88
2

Ответы:

Я думаю, что используя TRANSFORM (PIVOT), придется выполнить последующий запрос для расчета разницы.

Доступ читает 0 раз как 00:00:00. Для упрощения я удалил данные о времени из воскресных записей.

Рассмотрим этот подход:

SELECT Absen.Date, Absen.Status, Absen.Location, MasterDays.Day, MasterDays.Defin, 
MasterDays.Defrest, Max(IIf([InOut] = "IN",[Time])) AS [IN], 
Max(IIf([InOut] = "OUT",[Time])) AS Out, 
Format([OUT]-[IN]-Defrest,"hh:mm:ss") AS DurOfWork, 
Format([IN]-Defin, "hh:mm:ss") AS LateIn
FROM MasterDays INNER JOIN Absen ON MasterDays.Location = Absen.Location
AND (MASTERDAYS.DAY = FORMAT(ABSEN.DATE,'dddd'))
GROUP BY Absen.Date, Absen.Status, Absen.Location, MasterDays.Day, MasterDays.Defin, MasterDays.Defrest;

Решено

Если вы можете отказаться от использования TRANSFORM; тогда вы можете использовать что-то вроде:

SELECT 
    AIn.Date AS [DATE]
  , MD.day AS [Day of Week]
  , MI.NAMEID
  , AIn.status
  , IIF(AIn.STATUS = 'NOT PRESENT', '',MD.DEFREST) AS DEFREST
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AIn.TIME) AS [IN]
  , IIF(AIn.STATUS = 'NOT PRESENT', '',AOut.TIME) AS OUT
  , IIF(AIn.STATUS = 'NOT PRESENT', '',
          dateadd("s", datediff("s", AIn.TIME, AOut.TIME)-datediff("s", 0, MD.DefRest), 0)) AS DUROFWORK
  , IIF(cdate(AIn.[TIME]) > cdate(MD.DEFIN), format(dateadd("s", datediff("s", cdate(MD.DEFIN),
          cdate(AIn.[TIME])),0),"hh:nn:ss"),'') AS LATEIN
FROM ((ABSEN AS AIn 
       INNER JOIN MASTERDAYS AS MD ON AIn.LOCATION = MD.LOCATION) 
       INNER JOIN MASTERID AS MI ON MI.ID=AIn.ID)
       LEFT JOIN (SELECT * 
                  FROM ABSEN AS AOut1 
                  WHERE AOut1.INOUT='OUT') AS AOut ON (AOut.LOCATION=AIn.LOCATION) AND (AOut.DATE=AIn.DATE)
WHERE format(AIn.Date,"dddd")=MD.day
      AND  AIn.INOUT='IN';

Я предположил, что ваша «другая» таблица NAM В моем случае я сохранил столбцы «только время» в «ShortText» (возможно, вам придется настроить, если у вас другое.