Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 разница 2 таблиц с указанием кода ошибки  [new]
PacManStar
Member

Откуда:
Сообщений: 5
Здравствуйте!
Мне необходимо сверить платежи с контрагентом.
Как вывети разницу между таблицами я понимаю, но как узнать по каким полям эти различия?


Вывести:
reg_date_time,
card_number,
card_type,
card_sub_type,
begin_date_time,
sum_payment,
validator_id,
error_code

где error_code - это код ошибки который указывает на различия в полях

"1" если card_number есть в таблице TableA, но нет в таблице TableB
"2" если card_number есть в таблице TableB, но нет в таблице TableA
"4" если разница между TableA.reg_date_time и TableB.reg_date_time составляет более 5 минут
"8" если TableA.begin_date_time <> TableB.begin_date_time
"16" если TableA.sum_payment <> TableB.sum_payment

выборку из TableA ограничить:

reg_date_time BETWEEN '20150820 02:59:59' AND '20150821 03:00:00'
id_agents = 1
card_type = 150


Вот мой нерабочий вариант:


SELECT
  b.card_number,
  b.reg_date_time,
  b.card_sub_type,
  b.sum_payment,
  CASE
    WHEN a.card_number IS NULL THEN '1'
    WHEN ABS(DATEDIFF(MINUTE, a.reg_date_time, b.reg_date_time)) >= 4 THEN '4'
    WHEN a.sum_payment <> b.sum_payment THEN '16'
  ELSE '0'
  END AS error_code
FROM TableA a
LEFT JOIN TableB b
  ON a.card_number = b.card_number
WHERE 
  CASE
    WHEN a.card_number IS NULL THEN '1'
    WHEN ABS(DATEDIFF(MINUTE, a.reg_date_time, b.reg_date_time)) >= 4 THEN '4'
    WHEN a.sum_payment <> b.sum_payment THEN '16'
  ELSE '0'
  END <> 0 
  AND
  a.reg_date_time BETWEEN '20150820 02:59:59' AND '20150821 03:00:00'
  AND a.id_agents = 1
 AND a.card_type = 150

UNION

SELECT
 
  a.card_number,
  a.reg_date_time,
  a.card_sub_type,
  a.sum_payment,
  '2' AS error_code  

FROM TableB b
LEFT JOIN TableA a
  ON b.card_number = a.card_number
WHERE a.reg_date_time BETWEEN '20150820 02:59:59' AND '20150821 03:00:00'
AND a.id_agents = 1
AND a.card_type = 150
AND a.card_number IS null


CREATE TABLE TableB (
  reg_date_time datetime NOT NULL,
  card_number decimal(24) NOT NULL,
  card_type int NOT NULL,
  card_sub_type int NOT NULL,
  begin_date_time date NOT NULL,
  sum_payment int NOT NULL,
  validator_id int NOT NULL
) 


CREATE TABLE TableA (
  card_number decimal(24) NOT NULL,
  card_type int NOT NULL,
  card_sub_type int NOT NULL,
  reg_date_time datetime NOT NULL,
  sum_payment int NOT NULL,
  begin_date_time datetime NOT NULL,
  validator_id bigint NOT NULL,
  id_agents int NOT NULL
)


INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:00:00.000', 10000, 150, 1, '2015-08-23', 13697, 1751)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:03:00.000', 10001, 150, 8, '2015-08-24', 13381, 1692)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:06:00.000', 10002, 150, 1, '2015-08-21', 13228, 1029)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:09:00.000', 10003, 150, 4, '2015-08-24', 13287, 1181)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:12:00.000', 10004, 150, 3, '2015-08-22', 13551, 1206)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:15:00.000', 10005, 150, 6, '2015-08-24', 13213, 1027)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:18:00.000', 10006, 150, 1, '2015-08-24', 13519, 1000)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:21:00.000', 10007, 150, 2, '2015-08-21', 13018, 1275)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:24:00.000', 10008, 150, 7, '2015-08-25', 13378, 1739)
INSERT TableB(reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id) VALUES ('2015-08-20 07:27:00.000', 13465, 150, 3, '2015-08-22', 13070, 1628)
GO


INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10000, 150, 2, '2015-08-20 07:00:00.000', 13697, '2015-08-23 00:00:00.000', 1751, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10001, 150, 8, '2015-08-20 07:03:00.000', 13381, '2015-08-24 00:00:00.000', 1692, 1)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10002, 150, 1, '2015-08-20 07:06:00.000', 13228, '2015-08-21 00:00:00.000', 1029, 1)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10003, 150, 4, '2015-08-20 07:09:00.000', 13287, '2015-08-24 00:00:00.000', 1181, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10004, 150, 3, '2015-08-20 07:12:00.000', 13551, '2015-08-22 00:00:00.000', 1206, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10005, 150, 6, '2015-08-20 07:15:00.000', 13213, '2015-08-24 00:00:00.000', 1027, 1)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10006, 150, 1, '2015-08-20 07:18:00.000', 13519, '2015-08-24 00:00:00.000', 1000, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10007, 150, 2, '2015-08-20 07:21:00.000', 13018, '2015-08-21 00:00:00.000', 1275, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10008, 150, 7, '2015-08-20 07:24:00.000', 13378, '2015-08-25 00:00:00.000', 1739, 0)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10009, 150, 3, '2015-08-20 07:27:00.000', 13070, '2015-08-22 00:00:00.000', 1628, 1)
INSERT TableA(card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents) VALUES (10010, 252, 0, '2010-01-01 00:00:00.000', 1000, '2001-01-01 00:00:00.000', 1535, 1)
GO
26 авг 15, 10:42    [18068883]     Ответить | Цитировать Сообщить модератору
 Re: разница 2 таблиц с указанием кода ошибки  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE @TableB TABLE (
  reg_date_time datetime NOT NULL,
  card_number decimal(24) NOT NULL,
  card_type int NOT NULL,
  card_sub_type int NOT NULL,
  begin_date_time date NOT NULL,
  sum_payment int NOT NULL,
  validator_id int NOT NULL
) 

DECLARE @TableA TABLE (
  card_number decimal(24) NOT NULL,
  card_type int NOT NULL,
  card_sub_type int NOT NULL,
  reg_date_time datetime NOT NULL,
  sum_payment int NOT NULL,
  begin_date_time datetime NOT NULL,
  validator_id bigint NOT NULL,
  id_agents int NOT NULL
)

INSERT @TableB (reg_date_time, card_number, card_type, card_sub_type, begin_date_time, sum_payment, validator_id)
VALUES
	  ('2015-08-20 07:00:00.000', 10000, 150, 1, '2015-08-23', 13697, 1751)
	, ('2015-08-20 07:03:00.000', 10001, 150, 8, '2015-08-24', 13381, 1692)
	, ('2015-08-20 07:06:00.000', 10002, 150, 1, '2015-08-21', 13228, 1029)
	, ('2015-08-20 07:09:00.000', 10003, 150, 4, '2015-08-24', 13287, 1181)
	, ('2015-08-20 07:12:00.000', 10004, 150, 3, '2015-08-22', 13551, 1206)
	, ('2015-08-20 07:15:00.000', 10005, 150, 6, '2015-08-24', 13213, 1027)
	, ('2015-08-20 07:18:00.000', 10006, 150, 1, '2015-08-24', 13519, 1000)
	, ('2015-08-20 07:21:00.000', 10007, 150, 2, '2015-08-21', 13018, 1275)
	, ('2015-08-20 07:24:00.000', 10008, 150, 7, '2015-08-25', 13378, 1739)
	, ('2015-08-20 07:27:00.000', 13465, 150, 3, '2015-08-22', 13070, 1628)

INSERT @TableA (card_number, card_type, card_sub_type, reg_date_time, sum_payment, begin_date_time, validator_id, id_agents)
VALUES
	  (10000, 150, 2, '2015-08-20 07:00:00.000', 13697, '2015-08-23 00:00:00.000', 1751, 0)
	, (10001, 150, 8, '2015-08-20 07:03:00.000', 13381, '2015-08-24 00:00:00.000', 1692, 1)
	, (10002, 150, 1, '2015-08-20 07:06:00.000', 13228, '2015-08-21 00:00:00.000', 1029, 1)
	, (10003, 150, 4, '2015-08-20 07:09:00.000', 13287, '2015-08-24 00:00:00.000', 1181, 0)
	, (10004, 150, 3, '2015-08-20 07:12:00.000', 13551, '2015-08-22 00:00:00.000', 1206, 0)
	, (10005, 150, 6, '2015-08-20 07:15:00.000', 13213, '2015-08-24 00:00:00.000', 1027, 1)
	, (10006, 150, 1, '2015-08-20 07:18:00.000', 13519, '2015-08-24 00:00:00.000', 1000, 0)
	, (10007, 150, 2, '2015-08-20 07:21:00.000', 13018, '2015-08-21 00:00:00.000', 1275, 0)
	, (10008, 150, 7, '2015-08-20 07:24:00.000', 13378, '2015-08-25 00:00:00.000', 1739, 0)
	, (10009, 150, 3, '2015-08-20 07:27:00.000', 13070, '2015-08-22 00:00:00.000', 1628, 1)
	, (10010, 252, 0, '2010-01-01 00:00:00.000', 1000, '2001-01-01 00:00:00.000', 1535, 1)

SELECT
	  card_number = ISNULL(a.card_number, b.card_number)
	, reg_date_time = ISNULL(a.reg_date_time, b.reg_date_time)
	, card_sub_type = ISNULL(a.card_sub_type, b.card_sub_type)
	, sum_payment = ISNULL(a.sum_payment, b.sum_payment)
    , error_code =
		CASE
			WHEN b.card_number IS NULL THEN 1
			WHEN a.card_number IS NULL THEN 2
			WHEN DATEDIFF(MINUTE, a.reg_date_time, b.reg_date_time) > 5 THEN 4
			WHEN a.begin_date_time <> b.begin_date_time THEN 8
			WHEN a.sum_payment <> b.sum_payment THEN 16
			ELSE 0
		END
FROM (
	SELECT *
	FROM @TableA
	WHERE reg_date_time BETWEEN '20150820 02:59:59' AND '20150821 03:00:00'
		AND id_agents = 1
		AND card_type = 150
) a
FULL JOIN @TableB b ON a.card_number = b.card_number
26 авг 15, 10:53    [18068965]     Ответить | Цитировать Сообщить модератору
 Re: разница 2 таблиц с указанием кода ошибки  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 21100
PacManStar
где error_code - это код ошибки который указывает на различия в полях

"1" если card_number есть в таблице TableA, но нет в таблице TableB
"2" если card_number есть в таблице TableB, но нет в таблице TableA
"4" если разница между TableA.reg_date_time и TableB.reg_date_time составляет более 5 минут
"8" если TableA.begin_date_time <> TableB.begin_date_time
"16" если TableA.sum_payment <> TableB.sum_payment

SELECT ...,
  CASE WHEN TableB.card_number IS NULL THEN 1 ELSE 0 END 
+ CASE WHEN TableA.card_number IS NULL THEN 2 ELSE 0 END 
+ CASE WHEN DATEDIFF('n',TableA.reg_date_time,TableB.reg_date_time) BETWEEN -5 AND 5 THEN 0 ELSE 4 END 
+ CASE WHEN TableA.begin_date_time <> TableB.begin_date_time THEN 8 ELSE 0 END 
+ CASE WHEN TableA.sum_payment <> TableB.sum_payment THEN 16 ELSE 0 END
  AS error_code.
...
FROM TableA FULL OUTER JOIN TableB ON ...
WHERE ...
?
26 авг 15, 10:58    [18068999]     Ответить | Цитировать Сообщить модератору
 Re: разница 2 таблиц с указанием кода ошибки  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
поддерживаю вопрос @Akina...

но в любом случае там должен быть FULL JOIN, так что @PacManStar просто подретушируйте error_code исходя из своей задачи.
26 авг 15, 11:07    [18069036]     Ответить | Цитировать Сообщить модератору
 Re: разница 2 таблиц с указанием кода ошибки  [new]
PacManStar
Member

Откуда:
Сообщений: 5
Спасибо, очень помогли )
26 авг 15, 14:51    [18070652]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить