Запрос КТР и datetime соответствия


У меня есть две таблицы (они указаны ниже и вы можете использовать SQL, чтобы создать их)

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ETab')
DROP TABLE ETab;
GO

CREATE TABLE ETab 
    ([MRN] varchar(20), [LSPEC] varchar(2), [ADT] DATETIME, [SDT] DATETIME, [Source] varchar(20), [Enum] varchar(20));
GO

INSERT INTO ETab ([MRN], [LSPEC], [ADT], [SDT], [Source], [Enum]) 
VALUES 
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-04-01 00:00:00.000', 20),   CONVERT(datetime, '2017-04-30 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD04'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-05-01 00:00:00.000', 20),   CONVERT(datetime, '2017-05-31 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD05'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-06-01 00:00:00.000', 20),   CONVERT(datetime, '2017-06-30 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD06'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-07-01 00:00:00.000', 20),   CONVERT(datetime, '2017-07-31 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD07'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-08-01 00:00:00.000', 20),   CONVERT(datetime, '2017-08-31 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD08'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-09-01 00:00:00.000', 20),   CONVERT(datetime, '2017-09-30 00:00:00.000', 20),   'PRODPAT',  'HOMEBLD09'),
    ('HOMECARE',    'HQ',       CONVERT(datetime, '2017-04-01 00:00:00.000', 20),   CONVERT(datetime, '2017-04-30 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG04HM'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-05-01 00:00:00.000', 20),   CONVERT(datetime, '2017-05-31 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG05HM'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-06-01 00:00:00.000', 20),   CONVERT(datetime, '2017-06-30 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG06HM'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-07-01 00:00:00.000', 20),   CONVERT(datetime, '2017-07-31 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG07HM'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-08-01 00:00:00.000', 20),   CONVERT(datetime, '2017-08-31 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG08HM'),
    ('HOMECARE',    'HM',       CONVERT(datetime, '2017-09-01 00:00:00.000', 20),   CONVERT(datetime, '2017-09-30 00:00:00.000', 20),   'PRODPAT',  'HOMEDRG09HM'), 
    ('111824',      'UR',       CONVERT(datetime, '2017-09-22 00:00:00.000', 20),   CONVERT(datetime, '2017-09-22 00:00:00.000', 20),   'OP',   'OP1118240003'),
    ('111824',      'NL',       CONVERT(datetime, '2017-04-19 00:00:00.000', 20),   CONVERT(datetime, '2017-04-19 00:00:00.000', 20),   'OP',   'OP1118240001'),
    ('111824',      'MS',       CONVERT(datetime, '2017-06-30 00:00:00.000', 20),   CONVERT(datetime, '2017-06-30 00:00:00.000', 20),   'OP',   'OP1118240002'),
    ('111824',      'MS',       CONVERT(datetime, '2017-04-24 00:00:00.000', 20),   CONVERT(datetime, '2017-04-24 00:00:00.000', 20),   'IP',   'IP1118240001'),
    ('111824',      'MS',       CONVERT(datetime, '2017-04-28 00:00:00.000', 20),   CONVERT(datetime, '2017-04-28 00:00:00.000', 20),   'IP',   'IP1118240005'),
    ('111824',      'MS',       CONVERT(datetime, '2017-04-27 00:00:00.000', 20),   CONVERT(datetime, '2017-04-27 00:00:00.000', 20),   'IP',   'IP1118240004'),
    ('111824',      'MS',       CONVERT(datetime, '2017-04-26 00:00:00.000', 20),   CONVERT(datetime, '2017-04-26 00:00:00.000', 20),   'IP',   'IP1118240003'),
    ('111824',      'MS',       CONVERT(datetime, '2017-04-25 00:00:00.000', 20),   CONVERT(datetime, '2017-04-25 00:00:00.000', 20),   'IP',   'IP1118240002');
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UTab')
DROP TABLE UTab;
GO

CREATE TABLE UTab 
    (MRN varchar(20), SIDate DATETIME, LSPEC varchar(2), Source varchar(20), Enum varchar(20), Iteration varchar(20));
GO

INSERT INTO UTab 
    (MRN, SIDate, LSPEC, Source, Enum, Iteration) 
VALUES 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-17 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HQ', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-04-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-01 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-06-26 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-30 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL), 
('HOMECARE', CONVERT(datetime, '2017-05-04 00:00:00.000', 20), 'HM', 'N', NULL, NULL),
('111824', CONVERT(datetime, '2017-04-21 00:00:00.000', 20), 'MS', 'IP', NULL, NULL), 
('111824', CONVERT(datetime, '2017-04-24 00:00:00.000', 20), 'NL', 'OP', NULL, NULL), 
('111824', CONVERT(datetime, '2017-04-27 00:00:00.000', 20), 'NL', 'OP', NULL, NULL), 
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL), 
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL), 
('111824', CONVERT(datetime, '2017-04-20 00:00:00.000', 20), 'NL', 'OP', NULL, NULL);
GO

SELECT * FROM ETab
WHERE Source = 'PRODPAT' AND LSPEC = 'HM' 
GO

SELECT * FROM UTab  
WHERE LSPEC = 'HM';
GO

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '__Tmp')
DROP TABLE __Tmp;
GO

У меня есть C# приложение, в котором пользователь запрашивает определенные условия во время выполнения. Используя информацию, указанную пользователем, я сгенерировать SQL, который выполняется и обновления UTab.

Через сгенерированный SQL-код, я хотел бы проанализировать некоторые соответствия, который обновляет [UTab].[Enum] основываясь на ссылке поле [MRN] и будет ли [UTab].[SIDate] лежит в пределах [ETab].[ADT] и [ETab].[SDT] +- несколько дней до или после, которые указаны пользователем. Так, пользователь может указать две команды, первая:

MatchCmd:MRN,LSPEC:ETab:UTab:ADT:SDT:SIDate:0:1:'Iteration#1':WHERE [UTab].[Source] = 'OP' AND [UTab].[LSPEC] = [ETab].[LSPEC] 

Это говорит, матч MRN и LSPEC из исходной таблицы ETabобновление UTab где SIDate > ADT - 0 day(s) и SIDate < SDT + 1 day(s) и где [UTab].[Source] = 'OP' - никаких игр я знаком с "число итерации" Iteration#1.

Мой код генерирует следующий SQL:

;WITH cte AS (
    SELECT [ETab].[Enum] AS Enum, 
           [ETab].[MRN] AS Link, 
           [ETab].[ADT] AS ADT, 
           [ETab].[SDT] AS SDT, 
           [UTab].[SIDate] AS DT, 
           [ETab].[MRN] AS [MRN], 
           [ETab].[LSPEC] AS [LSPEC], 
           [ETab].[Source] AS [Source], 
           ROW_NUMBER() OVER (PARTITION BY [UTab].[MRN], [UTab].[LSPEC], [UTab].[SIDate] 
                ORDER BY ABS(DATEDIFF(mi, [UTab].[SIDate], [ETab].[ADT]))) AS Idx, 
           ABS(DATEDIFF(mi, [UTab].[SIDate], [ETab].[ADT])) AS Diff 
    FROM [UTab] 
        LEFT JOIN [ETab] ON [UTab].[MRN] = [ETab].[MRN] 
    WHERE ([UTab].[SIDate] BETWEEN 
        DATEADD(dd, -0, [ETab].[ADT]) AND 
        DATEADD(dd, 1, [ETab].[SDT]) AND [Iteration] IS NULL) 
            AND ETab.Source = 'OP' 
            AND UTab.LSPEC = ETab.LSPEC
)  SELECT * 
   INTO __Tmp 
   FROM cte; 
GO

Чтобы получить все записи, которые соответствуют критериям (укладка в окно времени и выполнение пользовательских оператор where). Потом обновить [UTab].[Enum] и [UTab].[Iteration] через другой созданного запроса

UPDATE [UTab] 
SET [ENum] = [__Tmp].[ENum], [Iteration] = N'Iteration#1' 
--SELECT __Tmp.ENum, __Tmp.Link, __Tmp.LSPEC, __Tmp.ADT, __Tmp.SDT, __Tmp.DT, __Tmp.Idx 
FROM [UTab] AS up 
    INNER JOIN [__Tmp] 
        ON [up].[MRN] = [__Tmp].[Link] 
WHERE __Tmp.Idx = '1' 
    AND up.SIDate = __Tmp.DT 
    AND up.Source = 'OP' 
    AND up.LSPEC = __Tmp.LSPEC;

Это, кажется, работает хорошо, но на некоторые вопросы:

К1. Есть что-то явно криво с методом/SQL, который я использую?

В2. Нужно ли добавить соединения для up.LSPEC = __Tmp.LSPEC спецификация или WHERE статья достаточно?

Спасибо за ваше время.



Комментарии