Sql TRANSFORM со временем расчета в MS Access
Я пытаюсь сделать Sql TRANSFORM со временем расчета в столбце и обратно.
Мне нужен ответ в 1 запросе, и я не хочу использовать эту функцию в доступе к MS, потому что я хочу использовать sql в vb.net
Пожалуйста, направь меня
Спасибо
Стол ABSEN
Стол MASTERDAYS
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');
Результат из кода
Желаемый результат
DUROFWORK = (ABSEN.Time IN - ABSEN.Time OUT - MASTERDAYS.DEFREST) LATEIN = (ОТСУТСТВИЕ.ВРЕМЯ ВХ. - MASTERDAYS.DEFIN)
Для статуса NOT PRESENT хочу сделать заготовку для DEFREST,IN,OUT,DUROFWORK,LATEIN
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
Вы создаете записи в Абсене за неявку? Почему Абсен показывает НАСТОЯЩЕЕ в воскресенье? Доступ читает 0 раз как 00:00:00.
@tinazmu, Is it always 1 IN and 0-1 OUT per day, per location? Ты прав, в день всегда 1 вход и 1 выход. Is there also a 'person' involved? Ты имеешь в виду, что люди определенно участвуют и выходят за 1 день
Так нужно делать расчеты на человека? Почему ваши выборочные данные этого не отражают?
@June7, извини, это опечатка. Access reads 0 time as 12:00:00 AM.И что мне делать?
@June7 So need to do calculations per person? Why does your sample data not reflect this? как я могу рассчитать время с помощью Transform Pivot sql, пожалуйста, помогите мне
Варианты борьбы с 0 раз; 1) оставить поле NULL; 2) не создавать записи о неявке; 3) IIf() Calc, с которым нужно иметь дело (что вы, кажется, и делаете).
Почему в каждой строке результатов указано 26 августа?
@tinazmu, извини, это опечатка
Ответы 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;
спасибо за ответ. Ваш ответ очень простой, но почему такой результат DurOfWork and LateIn #error
Я не получил #error в своем тесте.
для столбца TIME я использую данные типа Text / String
Почему? Я использовал тип даты/времени.
имеет ли значение, использую ли я строковый тип и, возможно, такую функцию datediff или другую
для упрощения записи я могу поставить 0 в столбце «Вход» и «Выход». Пожалуйста, направь меня
Строку необходимо преобразовать в значение даты/времени в Calc. DateDiff() выполнит неявное преобразование.
пожалуйста, помогите мне, я также обновил тип данных в сообщении, чтобы было проще
Если вы можете отказаться от использования 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» (возможно, вам придется настроить, если у вас другое.
спасибо за ответ, но почему вы используете hh:nn:ss, а не это hh:mm:ss
Я хочу спросить тебя. для 1. Я не вижу столбец Days в вашем ответном запросе. Есть ли он в этом коде строки MD.day as [Day of Week],, но он из таблицы MASTERDAYS и 2. Пример из таблицы Absen У меня есть столбец ID, тогда я хочу присоединить его к таблице MASTERID, чтобы возьмите колонку MASTERID.NAMEID Как мне адаптировать код из вашего ответа. Пожалуйста, направь меня
Я попробовал этот запрос, но записи не вернулись.
Вы должны сообщить нам типы данных, которые вы использовали.
@dlaksmi, у нас есть format(AIn.Date,"dddd")=MD.day в пунктеwhere; так это одно и то же.
@June7, я сохранил время в текстовом формате (в Access нет типа данных «время», но я думаю, я мог бы хранить их с частью даты «0»). Я протестировал его с опубликованными данными OP - как показано; Я не знаю, почему у тебя нет ссор; вы пытались немного его отладить: «почему он не возвращает ни одной строки»?
@dlaksmi, я обновил его, добавив идентификатор
@tinazmu Спасибо, но ID не со стола MASTERDAYS а с другого стола MASTERID
Я еще раз поменял, предположил, что вы присоединяетесь к MASTERID через MASTERID.ID. Было бы намного проще, если бы вы отредактировали свой вопрос и включили эти дополнительные требования в таблицу DDL (т. е. как мы узнаем, как эти таблицы связаны между собой?)
@tinazmu, твое обновление ответа идеально. Если у меня возникнут вопросы, я обязательно вам задам
@tinazmu, для других мне удалось отформатировать успешно, но код строки не работал FORMAT(AIn.Date,"dd-MMM-yy") AS [DATE] Результат в средстве просмотра отчетов 26-Aug-24 00:00:00 . Что-то не так с форматом?. AOut.Date Нужно ли это тоже форматировать, если да, то как?
Каков тип данных AIn.DATE? Какой у вас инструмент отчетности? если AIn.Date находится в тексте; функция ФОРМАТ была бесполезна; и похоже, что ваш инструмент отчетов считывает/сопоставляет его с полем DATE&TIME и форматирует его 26 августа 24 00:00:00; вам нужно сопоставить его с полем text/char.
Всегда ли это 1 вход и 0–1 выход в день для каждой локации? Есть ли в этом еще и «человек»?