SQL присоединиться к следующей доступной дате, если дата недействительна

Нужна помощь по запросу. У меня есть таблица клиентов, в которой регистрируются клиенты, связанные с базовым местоположением. И таблица местоположений с подробным описанием адресов для местоположения в определенный момент времени.

CREATE TABLE [dbo].[Customer](
   [ID] [int] NULL,
   [Date of Transfer] [datetime] NULL,
   [Old Location] [int] NULL,
   [New Location] [int] NULL)


INSERT INTO [dbo].[Customer] (ID, [Date of Transfer], [Old Location], [New Location])
VALUES  (1, '2016-07-01 00:00:00.000', 1001, 2200),
        (1, '2017-11-25 00:00:00.000', 2200, 3078),
        (1, '2018-01-01 00:00:00.000', 3078, 8500),
        (2, '2016-02-27 00:00:00.000', 5578, 9901),
        (2, '2017-06-22 00:00:00.000', 9901, 9001);

CREATE TABLE [dbo].[Location](
    [Location Code] [int] NULL,
    [Location Address] [nvarchar](50) NULL,
    [Date From] [date] NULL,
    [Date To] [date] NULL)


INSERT INTO [dbo].[Location] ([Location Code], [Location Address], [Date From], [Date To])
VALUES (1001, 'Logan Street', '1998-02-01 00:00:00.000', '2017-07-21 00:00:00.000'),
    (2200, 'Ashdown Avenue', '2014-01-31 00:00:00.000', NULL),
    (3078, 'Chessington Court', '2009-05-11 00:00:00.000', '2018-03-30 00:00:00.000'),
    (8500, 'Cambridge Green', '2018-01-15 00:00:00.000', '2018-02-15 00:00:00.000'),
    (8500, 'Blair Road', '2018-02-15 00:00:00.000', NULL),
    (5578, 'Aberdeen View', '2000-11-19 00:00:00.000', '2017-05-13 00:00:00.000'),
    (9901, 'Tay Road', '2013-02-02 00:00:00.000', '2018-01-22 00:00:00.000'),
    (9001, 'Wellington Way', '2015-02-16 00:00:00.000', '2018-02-22 00:00:00.000');

Я пытаюсь объединить обе таблицы, чтобы дать мне адрес места, связанного с клиентом, это точка, которую они переместили в другое место. Это отлично работает для большинства следующих

SELECT [ID]
  ,[Date of Transfer]
  ,[Old Location]
  ,O.[Location Address]
  ,O.[Date From]
  ,O.[Date To]
  ,[New Location]
  ,N.[Location Address]
  ,N.[Date From]
  ,N.[Date To]
FROM [dbo].[Customer]
Left Join [dbo].[Location] O on [Old Location] = O.[Location Code] and ([Date of Transfer] >= O.[Date From] OR O.[Date From] is NULL)  and ([Date of Transfer] <= O.[Date To] or O.[Date To] is NULL)
Left Join [dbo].[Location] N on [New Location] = N.[Location Code] and ([Date of Transfer] >= N.[Date From] OR N.[Date From] is NULL)  and ([Date of Transfer] <= N.[Date To] or N.[Date To] is NULL)
ORDER BY [ID], [Date of Transfer] ASC

У меня проблема в том, что таблица местоположений иногда обновлялась с опозданием, поэтому передача клиентов не присоединяется к какому-либо действительному местоположению. Я хотел бы добавить пункт, который проверяет дату, когда местоположение действительно. Итак, в приведенном выше примере я хотел бы связать перевод клиента 1 на 8500 с адресом Cambridge Green, обновленным примерно через 1 месяц после перевода. Кто-нибудь знает, как я могу это сделать.

Текущий пример вывода

ID          Date of Transfer        Old Location Location Address                                   Date From  Date To    New Location Location Address                                   Date From  Date To
----------- ----------------------- ------------ -------------------------------------------------- ---------- ---------- ------------ -------------------------------------------------- ---------- ----------
1           2016-07-01 00:00:00.000 1001         Logan Street                                       1998-02-01 2017-07-21 2200         Ashdown Avenue                                     2014-01-31 NULL
1           2017-11-25 00:00:00.000 2200         Ashdown Avenue                                     2014-01-31 NULL       3078         Chessington Court                                  2009-05-11 2018-03-30
1           2018-01-01 00:00:00.000 3078         Chessington Court                                  2009-05-11 2018-03-30 8500         NULL                                               NULL       NULL
2           2016-02-27 00:00:00.000 5578         Aberdeen View                                      2000-11-19 2017-05-13 9901         Tay Road                                           2013-02-02 2018-01-22
2           2017-06-22 00:00:00.000 9901         Tay Road                                           2013-02-02 2018-01-22 9001         Wellington Way                                     2015-02-16 2018-02-22

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

ПРИМЕЧАНИЕ. Таблица местоположений может иметь более одного адреса для каждого местоположения (в разные периоды. Поэтому я не могу использовать простое соединение.

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

ID          Date of Transfer        Old Location Location Address                                   Date From  Date To    New Location Location Address                                   Date From  Date To
----------- ----------------------- ------------ -------------------------------------------------- ---------- ---------- ------------ -------------------------------------------------- ---------- ----------
1           2016-07-01 00:00:00.000 1001         Logan Street                                       1998-02-01 2017-07-21 2200         Ashdown Avenue                                     2014-01-31 NULL
1           2017-11-25 00:00:00.000 2200         Ashdown Avenue                                     2014-01-31 NULL       3078         Chessington Court                                  2009-05-11 2018-03-30
1           2018-01-01 00:00:00.000 3078         Chessington Court                                  2009-05-11 2018-03-30 8500         Cambridge Green                                    2018-01-15 2018-02-15
2           2016-02-27 00:00:00.000 5578         Aberdeen View                                      2000-11-19 2017-05-13 9901         Tay Road                                           2013-02-02 2018-01-22
2           2017-06-22 00:00:00.000 9901         Tay Road                                           2013-02-02 2018-01-22 9001         Wellington Way                                     2015-02-16 2018-02-22

Не могли бы вы опубликовать фактический и ожидаемый результат для данных в вашей таблице?

Radim Bača 11.04.2018 12:34
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
55
2

Ответы 2

Попробуй это.

SELECT [ID]
  ,[Date of Transfer]
  ,[Old Location]
  ,O.[Location Address]
  ,O.[Date From]
  ,O.[Date To]
  ,[New Location]
  ,N.[Location Address]
  ,N.[Date From]
  ,N.[Date To]
FROM [dbo].[Customer]
Left Join [dbo].[Location] O on [Old Location] = O.[Location Code] and ([Date of Transfer] >= O.[Date From] OR O.[Date From] is NULL)  and ([Date of Transfer] <= O.[Date To] or O.[Date To] is NULL)
Join [dbo].[Location] N on [New Location] = N.[Location Code] and ([Date of Transfer] >= N.[Date From] OR N.[Date From] is NULL)  and ([Date of Transfer] <= N.[Date To] or N.[Date To] is NULL)
ORDER BY [ID], [Date of Transfer] ASC

Спасибо, но это не учитывает возможность того, что другое местоположение с этим кодом также может быть включено в таблицу с другим адресом.

Paul Tervit 11.04.2018 14:06

Вы можете поделиться желаемым результатом с полным корпусом?

M Danish 11.04.2018 14:28

Попробуй это.

SELECT [ID]
  ,[Date of Transfer]
  ,[Old Location]
  ,O.[Location Address]
  ,O.[Date From]
  ,O.[Date To]
  ,[New Location]
  ,N.[Location Address]
  ,N.[Date From]
  ,N.[Date To]
FROM [dbo].[Customer]
Left Join [dbo].[Location] O on [Old Location] = O.[Location Code] and ([Date of Transfer] >= O.[Date From] OR O.[Date From] is NULL)  and ([Date of Transfer] <= O.[Date To] or O.[Date To] is NULL)
Left Join [dbo].[Location] N on [New Location] = N.[Location Code] and ([Date of Transfer] >= N.[Date From] OR N.[Date From] is NULL or N.[Date To] is NULL)  and ([Date of Transfer] <= N.[Date To] or N.[Date To] is NULL)
ORDER BY [ID], [Date of Transfer] ASC

Еще раз спасибо, но вы можете неправильно понять мою проблему. Мне нужно присоединиться к значению между двумя датами. Если теперь значение совпадает между двумя датами, мне нужно найти место с ближайшей датой после даты передачи.

Paul Tervit 11.04.2018 14:37

Другие вопросы по теме