Повторяющиеся строки из правого соединения

Мое приложение сохраняет журналы, которые необходимо снимать хотя бы один раз в течение каждого из трех разных периодов времени в течение дня. Так что в идеале 3 журнала в день, каждый с уникальным идентификатором периода времени. Мне нужно написать отчет об исключении (MSSQL 2008), который покажет, когда период времени пропущен для любого дня. У меня есть таблица LogTimePeriods, которая содержит 3 строки для каждого из периодов времени. Таблица журналов содержит LogTimePeriodID, поэтому мне не нужно выполнять какую-либо логику, чтобы увидеть, к какому периоду времени принадлежит журнал (это делается через приложение).

Я знаю, что мне нужно что-то вроде соединения справа/слева, чтобы попытаться сопоставить все LogTimePeriodID для каждой строки журнала для данной даты. Кажется, я не могу добиться никакого прогресса. Любая помощь приветствуется! Спасибо за чтение.

скрипт SQL

РЕДАКТИРОВАТЬ: желаемый результат ниже

Дата | LogPeriodID
6/3 | 3
6/5 | 2
6/5 | 3


sql left-join right-join
person koolaide    schedule 05.06.2014    source источник
comment
Итак, каков будет ожидаемый результат запроса?   -  person cy3er    schedule 05.06.2014
comment
Список дат и идентификаторов LogTimePeriodID, когда журнал не был завершен. В моей скрипте 3 июня завершено только 2 из 3 периодов времени, а 5 июня — только 1 из 3 (один и тот же период времени завершен дважды). Пожалуйста, посмотрите мое редактирование исходного вопроса, например вывод.   -  person koolaide    schedule 05.06.2014

Ответы (2)


arrow_upward
1
arrow_downward

Ваш SQL Fiddle настроен на использование MYSQL, а не SQL Server 2008, поэтому я не могу проверить свой ответ на ваших данных: однако, основываясь на моем понимании ваших требований и предполагая, что вы запрашиваете базу данных SQL 2008, следующий пример должен работать для вас (ссылки на мои табличные переменные, очевидно, будут заменены вашими реальными таблицами).

DECLARE @StartDate DATE = '06/04/2014'
DECLARE @EndDate DATE = GETDATE();
DECLARE @LogTimePeriod TABLE (LogTimePeriodID INT IDENTITY(1,1), TimePeriod VARCHAR(20))
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '00:00 - 07:59'
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '08:00 - 15:59'
INSERT INTO @LogTImePeriod (TimePeriod) SELECT '16:00 - 23:59'


DECLARE @logs TABLE (LogDataID INT IDENTITY(1,1), LogDate DATE, SomeInformation VARCHAR(10), LogTimePeriodID INT)
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'abc', '6/4/2014', 1
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'def', '6/4/2014', 2
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'ghi', '6/4/2014', 3
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'abc', '6/5/2014', 1
INSERT INTO @logs (SomeInformation, LogDate, LogTimePeriodID) SELECT 'def', '6/5/2014', 2;


WITH dates AS (
     SELECT CAST(@StartDate AS DATETIME) 'date'
     UNION ALL
     SELECT DATEADD(dd, 1, t.date) 
       FROM dates t
      WHERE DATEADD(dd, 1, t.date) <= @EndDate)

SELECT ltp.LogTimePeriodID, ltp.TimePeriod, dates.date
FROM 
    @LogTimePeriod ltp
     INNER JOIN 
    dates ON 1=1
     LEFT JOIN 
    @logs ld ON 
        ltp.LogTimePeriodID = ld.LogTimePeriodID AND
        dates.date = ld.LogDate
WHERE ld.LogDataID IS NULL
 OPTION (MAXRECURSION 1000) -- 0 is unlimited, 1000 limits to 1000 rows
person AHiggins    schedule 05.06.2014
comment
Да!! Присоединение к списку дат было ключевой частью, которую я упустил, это довольно умно. Также я извиняюсь, я не знал, что скрипка SQL может быть настроена на другой механизм БД. Спасибо, что научил меня 2 вещам сегодня :) - person koolaide; 05.06.2014
comment
Пожалуйста. Кто-то намного умнее меня придумал трюк со списком дат, и здесь он хорошо работает, потому что вам нужно эффективно создать одну группу данных со всеми возможными комбинациями, прежде чем вы сможете выяснить, какие комбинации отсутствуют. - person AHiggins; 05.06.2014

arrow_upward
0
arrow_downward

В SQLServer 2008 есть ключевое слово EXCEPT, которое вычитает второй набор записей из первого.
В этом случае можно сгенерировать все возможные журналы и удалить из них те, которые находятся в таблице журналов, что оставит журналы, отсутствующие в таблице. .

SELECT DISTINCT StartDateTime, StartTime, EndTime
FROM   Logs
       CROSS JOIN LogTimePeriods
EXCEPT
SELECT StartDateTime, StartTime, EndTime
FROM   LogTimePeriods ltp
       LEFT  JOIN logs l ON l.LogTimePeriodID = ltp.LogTimePeriodID
ORDER BY StartDateTime, StartTime

демонстрация SQLFiddle с вашими данными, преобразованными в SQLServer 2008

person Serpiton    schedule 05.06.2014