Some hand-matching of internal/external records may be needed even in an automated reconciliation. The matches will be stored in a database. Rules on matching can be enforced in the application, but there are often good reasons to place them in the database. In Sql Server, a filtered index (with a WHERE clause, in this case excluding Nulls) on the table can make this easier and more correct.
The script below creates two temporary tables. Rows are matched on “TransactionDate” and “Description”. Where no unique match is found, they need to be linked manually, by a common value in the “ManualMatchKey” column.
“Good” Nulls
The benefit of the filtered index is that you can “manage the exceptions”. The ManualMatchKey column is irrelevant where the rows are matched automatically. Null is the correct value, indicating “not applicable”. A filtered index provides this – no value unless it’s appropriate, and all values which do appear will be unique at TransactionDate/Description level (if you must define the ManualMatchKey column as NOT NULL, perhaps you could use a minus value, and change the index WHERE clause to exclude it).
The same benefit can be realised where your application harmonises the ordering of two datasets. With a nullable “ManualOrdering” column, the automatic ordering can be over-ridden where necessary, with values that are unique at the level defined in the filtered index.
Demo Script
To keep it simple I have left out the intermediate and output tables which you’d find in a production system. I’ve not put in any logic preventing one-sided matches (this could be done in code – no triggers please – or with a common parent table). I have assumed that rows match one-to-one. One-to-many matching could be enabled by adding a match “sub-key” column, and making changes to the indexing.
I have not performance-tested these techniques, so if your data volumes are large you’ll need to do that.
--run this with TEXT output if you want the PRINT messages in the right place--
/*
DROP TABLE #OwnRecords
DROP TABLE #ExternalRecords
GO
*/
SET NOCOUNT ON
SELECT @@VERSION --Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
CREATE TABLE #OwnRecords
(
Id int NOT NULL IDENTITY(1,1) PRIMARY KEY
,TransactionDate date NOT NULL
,Description varchar(50) NOT NULL
,Amount money NOT NULL
,ManualMatchKey int NULL
,MatchedBy nvarchar(50) NULL
)
CREATE TABLE #ExternalRecords
(
Id int NOT NULL IDENTITY(1,1) PRIMARY KEY
,TransactionDate date NOT NULL
,Description varchar(50) NOT NULL
,Amount money NOT NULL
,ManualMatchKey int NULL
,MatchedBy nvarchar(50) NULL
)
--filtered indexes constrain values to uniqueness over index columns but ignore nulls--
CREATE UNIQUE INDEX #OwnRecords_MatchKey_UQFI
ON #OwnRecords(TransactionDate, ManualMatchKey)
WHERE ManualMatchKey Is Not Null
CREATE UNIQUE INDEX #ExternalRecords_MatchKey_UQFI
ON #ExternalRecords(TransactionDate, ManualMatchKey)
WHERE ManualMatchKey Is Not Null
INSERT INTO
#OwnRecords
(
TransactionDate
,Description
,Amount
)
VALUES('19 Feb 2016','Payment',15.00)
,('19 Feb 2016','Payment',10.00)
,('19 Feb 2016', 'Payment',12.00)
INSERT INTO
#ExternalRecords
(
TransactionDate
,Description
,Amount
)
VALUES('19 Feb 2016', 'Payment',12.00)
,('19 Feb 2016','Payment',10.00)
,('19 Feb 2016','Payment',15.00)
PRINT 'Euchhh! Cartesian Product!'
SELECT #O.*
,#S.*
FROM
#OwnRecords #O
--would probably use set operators here instead of FULL JOIN for perf--
FULL JOIN #ExternalRecords #S
ON #O.TransactionDate = #S.TransactionDate
And #O.Description = #S.Description
UPDATE #OwnRecords
SET ManualMatchKey = 1
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 15
UPDATE #ExternalRecords
SET ManualMatchKey = 1
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 15
UPDATE #OwnRecords
SET ManualMatchKey = 2
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 10
UPDATE #ExternalRecords
SET ManualMatchKey = 2
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 10
UPDATE #OwnRecords
SET ManualMatchKey = 3
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 12
UPDATE #ExternalRecords
SET ManualMatchKey = 3
,MatchedBy = SUSER_SNAME()
WHERE TransactionDate = '19 Feb 2016' And Amount = 12
PRINT 'Linked manually, now we get one match per row'
SELECT #O.*
,#S.*
FROM
#OwnRecords #O
--for performance in PROD would probably use set operators instead of FULL JOIN--
FULL JOIN #ExternalRecords #S
ON #O.TransactionDate = #S.TransactionDate
And #O.Description = #S.Description
And (
#O.ManualMatchKey = #S.ManualMatchKey
Or (#O.ManualMatchKey Is Null And #S.ManualMatchKey Is Null)
)
PRINT 'Add rows that are unique at TransactionDate/Description level - they match automatically'
INSERT INTO
#OwnRecords
(
TransactionDate
,Description
,Amount
)
VALUES('18 Feb 2016','Payment',15.00)
INSERT INTO
#ExternalRecords
(
TransactionDate
,Description
,Amount
)
VALUES('18 Feb 2016','Payment',15.00)
SELECT #O.*
,#S.*
FROM
#OwnRecords #O
--for performance in PROD would probably use set operators instead of FULL JOIN--
FULL JOIN #ExternalRecords #S
ON #O.TransactionDate = #S.TransactionDate
And #O.Description = #S.Description
And (
#O.ManualMatchKey = #S.ManualMatchKey
Or (#O.ManualMatchKey Is Null And #S.ManualMatchKey Is Null)
)
PRINT 'ManualMatchKey is unique only at TransactionDate/Description level so values can repeat'
INSERT INTO
#ExternalRecords
(
TransactionDate
,Description
,Amount
,ManualMatchKey
,MatchedBy
)
VALUES('20 Feb 2016','Payment',15.00, 1, SUSER_SNAME())
SELECT * FROM #ExternalRecords WHERE ManualMatchKey = 1
PRINT 'Duplicate ManualMatchKeys not allowed at TransactionDate/Description level'
UPDATE #OwnRecords
SET ManualMatchKey = 1
WHERE Id = 2
blog.philipsymons.com 