В данный момент я сталкиваюсь со стенами, пытаясь написать операторы, которые возвращали бы определенные записи в таблице, которые могут иметь какое-то дублированное уникальное значение (идентификатор сотрудника). См. пример ниже: (Таблица ниже была импортирована с использованием сохраненного импорта, и все поля имеют тип данных ShortText, и при импорте нет настройки PK или индексов)
Требование состоит в том, чтобы создать отчет, который возвращает записи, используя следующие критерии по порядку:
Единственный способ, которым я могу это сделать, - это несколько операторов:
Это логика, о которой я думал, но пока застрял на ее построении (кажется, что слишком ветрено). Конечный результат, который я ищу, будет следующим:
Любая помощь будет замечательной и очень признательна, спасибо!
Итак, я нашел решение, хотя я уверен, что его можно упростить, чтобы получить тот же конечный результат. Ниже показано, что я придумал, выполнив 3 шага (вероятно, они отнимают много ресурсов и времени, особенно в таблице с более чем 60 тыс. записей):
После этого объедините все вместе в целевую таблицу.
Извините, я проверил это с другой таблицей, которую я сделал на своем рабочем компьютере для тестирования, но это почти то же самое.
Это первая часть (только для добавления открытых счетов):
SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] AS RequestDate2
INTO tblTestOutput
FROM tblTest
INNER JOIN (
SELECT qryFullColumns.[EMPID],
qryFullColumns.[AccountNumber],
qryFullColumns.[Status],
qryFullColumns.[RequestDate],
qryFullColumns.[LastRequestDate]
FROM (
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) AS EMPID,
tblTest.[AccountNumber],
tblTest.[Status],
IIf(Not tblTest.[RequestDate] Is Null, tblTest.[RequestDate],'12/31/2999') AS RequestDate,
tblTest.[LastRequestDate]
FROM tblTest) AS qryFullColumns
INNER JOIN (
SELECT qryMaxReqDate.EMPID,
Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate
FROM (
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) AS EMPID,
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') AS MaxReqDate
FROM tblTest
) AS qryMaxReqDate
GROUP BY EMPID) AS qryMaxReqDate
ON qryFullColumns.[EMPID] = qryMaxReqDate.[EMPID]
WHERE CDate(qryFullColumns.[RequestDate]) = qryMaxReqDate.[MaxReqDate]
) AS qryMaxReqDate
ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qryMaxReqDate.[EMPID]
WHERE qryFulLColumns.[Status] = 'Open' AND tblTest.[Status] <> 'Closed';
Для второго шага добавить только неповторяющиеся записи с закрытым статусом:
INSERT INTO tblTestOutput ([EMPID], [AccountNumber], [Status], [RequestDate], [RequestDate2])
SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] as [RequestDate2]
FROM tblTest
INNER JOIN (
SELECT qryMaxReqDate.EMPID, Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate
FROM (
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) As EMPID,
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') As MaxReqDate
FROM tblTest
) As qryMaxReqDate
GROUP BY EMPID HAVING Count([EMPID]) =1
) As qrySub ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qrySub.[EMPID]
WHERE tblTest.[Status] = 'Closed'
И последний шаг — добавить дубликаты аккаунтов, закрытых с датой последнего запроса:
SELECT tblTest.[EMPID], tblTest.[AccountNumber], tblTest.[Status], tblTest.[RequestDate], tblTest.[RequestDate] as [RequestDate2]
FROM tblTest
INNER JOIN (
SELECT qryMaxReqDate.EMPID, Max(CDate(qryMaxReqDate.MaxReqDate)) AS MaxReqDate
FROM (
SELECT IIf(Isnull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) As EMPID,
IIf(Not [RequestDate] Is Null,[RequestDate],'12/31/2999') As MaxReqDate
FROM tblTest) As qryMaxReqDate
GROUP BY EMPID HAVING Count([EMPID]) >1
) As qrySub ON IIf(IsNull(tblTest.[EMPID]), 'ACT' & Right(tblTest.[AccountNumber],6), tblTest.[EMPID]) = qrySub.[EMPID]
WHERE tblTest.[Status] = 'Closed' AND Not tblTest.[LastRequestDate] Is Null
UNION и NOT IN возвращают желаемый результат для заданных выборочных данных.
SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest
WHERE Status = "Open"
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest
WHERE NOT LastDateChange Is Null
AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open")
UNION SELECT EmpID, ActNumber, Status, ReqDate, LastDateChange FROM tblTest
WHERE LastDateChange Is Null
AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open")
AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE NOT LastDateChange Is Null
AND EmpID NOT IN (SELECT EmpID FROM tblTest WHERE Status = "Open"))
;
Обновлено - KNog Это пересмотренное выше предложение, и я получил правильный результат.
SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange
FROM tblTest
INNER JOIN (
SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Open' GROUP BY EmpID) As MaxOpnAct
ON tblTest.EmpID = MaxOpnAct.EmpID
WHERE Status = "Open"
UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange
FROM tblTest
INNER JOIN (
SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)=1) As MaxClsNonDup
ON tblTest.EmpID = MaxClsNonDup.EmpID
UNION SELECT tblTest.EmpID, ActNumber, Status, ReqDate, LastDateChange
FROM tblTest
INNER JOIN (
SELECT EmpID, Max(CDate(ReqDate)) As MaxDate From tblTest WHERE Not EmpID Is Null AND Status = 'Closed' GROUP BY EmpID HAVING Count(EmpID)>1) As MaxClsDup
ON tblTest.EmpID = MaxClsDup.EmpID
WHERE NOT LastDateChange Is Null
AND Status = 'Closed'
AND CDate(ReqDate) = MaxDate
Когда я реализовал описанный выше метод в своем реальном проекте, я заметил, что в ответ получаю повторяющиеся записи, поскольку UNION объединяет все записи вместе, независимо от того, имеет ли уже предыдущий результат запроса тот же соответствующий EMPID или нет. Итак, что я сделал, чтобы обойти это, вместо использования UNION, я просто (в моем случае) создаю таблицу с первым набором запросов, а затем вставляю следующий во вновь созданную таблицу, опуская EMPID, где он соответствует в Таблица. Затем то же самое делается для последнего, что дает окончательный результат, который я ожидал.
Спасибо, это сработало намного лучше. Однако он не выводил те, которые «Закрытые» не дублируются, и те, которые дублируются с «Закрытыми», которые затем отфильтровывались с использованием Not LastDateChange Is Null. Это, вероятно, моя вина за неясные требования. Тем не менее, это здорово, как начало для меня, спасибо! Я также переписал свои шаги ниже, которые намного более сжаты, и, конечно, я также могу включить UNION между тремя запросами.