Efficient Comparison of variable length lists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Efficient Comparison of variable length lists

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-04 : 09:49:07

Hello all. Long time no posts. Been very busy.

Hard time describing what I want so here's a dataset instead


DECLARE @personIdentity TABLE (
	[Id] INT
	, [name] VARCHAR(512)
	)

DECLARE @benefit TABLE (
	[ID] INT
	, [name] VARCHAR(255)
	)

DECLARE @selection TABLE (
	[ID] INT
	, [personIdentityID] INT
	, [benefitID] INT
	, [effectiveDate] DATETIME
	)

DECLARE @selectionComponent TABLE (
	[ID] INT
	, [selectionID] INT 
	, [cost] MONEY
	, [description] VARCHAR(512)
	)

DECLARE @coveredPersons TABLE (
	[selectionComponentID] INT
	, [personIdentityID] INT

	PRIMARY KEY (
		[selectionComponentID]
		, [personIdentityID]
		)
	)

-- SAMPLE DATA
INSERT @personIdentity ([Id], [name])
      SELECT 1, 'Fred'
UNION SELECT 1045, 'Wilma'
UNION SELECT 2312, 'Pebbles'
UNION SELECT 9932, 'Dino'
UNION SELECT 223, 'Barney'
UNION SELECT 212, 'Bam-Bam'

INSERT @benefit ([Id], [name])
      SELECT 1, 'Bikes'
UNION SELECT 2, 'Private Medical'
UNION SELECT 3, 'Travel Insurance'

INSERT @selection ([Id], [personIdentityID], [benefitID], [effectiveDate])
      SELECT 1, 1, 1, '20100101'
UNION SELECT 2, 1, 2, '20100101'
UNION SELECT 3, 1, 3, '20100101'
UNION SELECT 4, 1, 2, '20100501'

INSERT @selectionComponent ([ID], [SelectionID], [cost], [description])
      SELECT 1, 1, 500, 'Letter of Collection'
UNION SELECT 2, 2, 100, 'Family Cover'
UNION SELECT 3, 3, 200, 'Self and Children'
UNION SELECT 4, 4, 500, 'Family Cover'

INSERT @coveredPersons ([selectionComponentID], [personIdentityID])
      SELECT 2, 1045
UNION SELECT 2, 2312
UNION SELECT 2, 9932
UNION SELECT 3, 223
UNION SELECT 3, 212
UNION SELECT 4, 1045
UNION SELECT 4, 212

-- Sample report
SELECT
	p.[name] AS [Employee]
	, b.[name] AS [benefit]
	, s.[effectiveDate] AS [Effective Date]
	, sc.[description] AS [Cover Level]
	, pd.[name] AS [Dependant Covered]
FROM
	@personIdentity p
	JOIN @selection s ON s.[personIdentityID] = p.[ID]
	JOIN @selectionComponent sc ON sc.[selectionID] = s.[ID]
	JOIN @benefit b On b.[ID] = s.[benefitID]
	LEFT JOIN @coveredPersons cp On cp.[selectionComponentID] = sc.[ID]
	LEFT JOIN @personIdentity pd ON pd.[ID] = cp.[personIDentityID]
ORDER BY
	p.[name]
	, b.[name]
	, s.[effectiveDate]
	, pd.[name]

Results:


Employee   benefit            Effective Date          Cover Level           Dependant Covered
---------- ------------------ ----------------------- --------------------- -----------------
Fred       Bikes              2010-01-01 00:00:00.000 Letter of Collection  NULL
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Dino
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Pebbles
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Wilma
Fred       Private Medical    2010-05-01 00:00:00.000 Family Cover          Bam-Bam
Fred       Private Medical    2010-05-01 00:00:00.000 Family Cover          Wilma
Fred       Travel Insurance   2010-01-01 00:00:00.000 Self and Children     Bam-Bam
Fred       Travel Insurance   2010-01-01 00:00:00.000 Self and Children     Barney

So what I have here is a report that shows which dependants our employee (Fred) has covered for each of his selected benefits. As you can see the benefit for Private Medical has 2 different selections (for different dates)

Now I'm writing an incremental changes report that needs to check for dependant cover changes (along with a whole host of other stuff) (you can change the covered dependants on each selection). So I'd need a way to compare two different chunks from coveredPersons. Ideally I'd like to do this as quickly as possible.

The current method I'm using is this:


-- Way to compare covered persons
SELECT
	sc.[ID]
	, cov.[persons]
FROM
	@selectionComponent sc

	CROSS APPLY (
		SELECT
			CAST(cp.[personIdentityID] AS VARCHAR(11)) + '-'
		FROM
			@coveredPersons cp
		WHERE
			cp.[selectionComponentID] = sc.[ID]
		ORDER BY
			cp.[personIdentityID]
		FOR XML PATH ('')
		)
		cov ([persons])

So I'm concatenating all the personIdentityId's into a string which I can then use with a simple <> operator to determine if dependants have changed on the selection.
(the results look like)


ID          persons
----------- -----------------
1           NULL
2           1045-2312-9932-
3           212-223-
4           212-1045-

Because the list of covered dependants is variable (there can be any number of covered dependants per selectionComponet) this was the quickest way I could think of to compare two lists of coveredPersons.

I haven't finished the report so I haven't been able to do any performance testing but I'm guessing that one of you knows a better way?

IS there are better way?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:05:15

What is the expected result?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-04 : 11:22:26

Hi Peso,

I just want a way to determine if the dependants have changed from one selection to the next (per benefit and person). (all of this example is extremely simplified)

I've got a lot of rules to do with 2d time which I've already implemented. what would be idea would be something I could JOIN on to the list of selectionComponents that I already have when I'm doing my comparison part. I didn't want to confuse the issue with all of that.

What I've got right now is a CTE that returns an underscore (_) seperated string of personIdentityId's from coveredPerson.


, dependantHash ([selectionComponentID], [depList])
	AS (
		SELECT
			sc.[ID] AS [SelectionComponentID]
			, ISNULL(cov.[persons], '')
		FROM
			selection s
			JOIN selectionComponent sc ON sc.[selectionID] = s.[ID]

			CROSS APPLY (
				SELECT
					CAST(cp.[personIdentityID] AS VARCHAR(12)) + '_'
				FROM
					coveredPersons cp
				WHERE
					cp.[selectionComponentID] = sc.[ID]
					AND cp.[personIdentityID] <> s.[personIdentityID]
				ORDER BY
					cp.[personIdentityID]
				FOR XML PATH ('')
				)
				cov ([persons])
	)

I'm using this to JOIN onto selectionComponent and I have a clause like this in the logic that does the comparison:


-- And there is a change in cover level / coverage level
AND (
	sc.[description] <> scRow.[description]
	OR sc.[employeeSelectionCost] <> scRow.[employeeSelectionCost]
	OR dh.[depList] <> cr.[dependantHash]
	)

I'm using a recursive WHILE loop to work backwards from the most recent change to the earliest allowed by the date range passed for the report.

Does this all make sense?
Does my approach even sound sensible or is a terrible hack that I need to change.

I'll rewrite and post the logic for my current report but it's pretty convoluted and deals with a lot of other stuff other than the dependants.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 11:32:35

quote:

Originally posted by Transact Charlie


Employee   benefit            Effective Date          Cover Level           Dependant Covered
---------- ------------------ ----------------------- --------------------- -----------------
Fred       Bikes              2010-01-01 00:00:00.000 Letter of Collection  NULL
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Dino
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Pebbles
Fred       Private Medical    2010-01-01 00:00:00.000 Family Cover          Wilma
Fred       Private Medical    2010-05-01 00:00:00.000 Family Cover          Bam-Bam
Fred       Private Medical    2010-05-01 00:00:00.000 Family Cover          Wilma
Fred       Travel Insurance   2010-01-01 00:00:00.000 Self and Children     Bam-Bam
Fred       Travel Insurance   2010-01-01 00:00:00.000 Self and Children     Barney

So from this untouched and original data, you want to display the red part only? Still displaying all columns as is?

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-04 : 11:45:59

Sorry -- I've obviously not explained myself at all well.

That sample result is only really to show what the data looks like as is.

What I want is a nice way of determining whether the depends have changed from one selection to the next. So for example this SQL


SELECT
	p.[name] AS [Person]
	, b.[name] AS [benefit]
	, s.[effectiveDate] AS [effective]
	, sc.[description] AS [Cover Level]
	, '????' AS [Dependants Changed]
FROM
	@personIdentity p
	JOIN @selection s ON s.[personIdentityID] = p.[Id]
	JOIN @benefit b ON b.[id] = s.[benefitID]
	JOIN @selectionComponent sc ON sc.[selectionId] = s.[ID]

Generated this result set:



Person  benefit           effective               Cover Level           Dependants Changed
------- ----------------- ----------------------- --------------------- ------------------
Fred    Bikes             2010-01-01 00:00:00.000 Letter of Collection  ????
Fred    Private Medical   2010-01-01 00:00:00.000 Family Cover          ????
Fred    Travel Insurance  2010-01-01 00:00:00.000 Self and Children     ????
Fred    Private Medical   2010-05-01 00:00:00.000 Family Cover          ????

And I want to populate the [Dependants Changed] Column. The only row where the dependants would have changed is the private medical line effective 2010-05-01.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 12:05:24

What about a cursor for this?

-- Sample report
SELECT
	p.[name] AS [Employee]
	, b.[name] AS [benefit]
	, s.[effectiveDate] AS [Effective Date]
	, sc.[description] AS [Cover Level]
	, CHECKSUM_AGG(pd.Id) AS theTrickValue
FROM
	@personIdentity p
	JOIN @selection s ON s.[personIdentityID] = p.[ID]
	JOIN @selectionComponent sc ON sc.[selectionID] = s.[ID]
	JOIN @benefit b On b.[ID] = s.[benefitID]
	LEFT JOIN @coveredPersons cp On cp.[selectionComponentID] = sc.[ID]
	LEFT JOIN @personIdentity pd ON pd.[ID] = cp.[personIDentityID]
GROUP BY
	p.[name]
	, b.[name]
	, s.[effectiveDate]
	, sc.[description]
ORDER BY
	p.[name]
	, b.[name]
	, s.[effectiveDate]
	, sc.[description]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 12:29:50

I have an other idea. Give me some time to verify, please.

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-05 : 04:16:11

The words I never expected you to ever say:

quote:

What about a cursor for this?

However, it doesn't look like you've actually used a cursor.....

Thanks -- CHECKSUM_AGG looks exactly like what I want. I didn't know such an aggregate function existed.

I'll stay glued to see what this 'other idea' is

Cheers Peso.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-05 : 04:21:29

Is there any information on how unreliable CHECKSUM_AGG is?

The help for CHECKSUM states:

quote:

If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.

How small is small?
How would I even start finding out this kind of information?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-05 : 04:26:26

CHECKSUM_AGG() == Seems to be XOR
http://dbwhisperer.blogspot.com/2009/02/checksumagg-very-nifty-function.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-05 : 07:45:37

See this for how CHECKSUM and BINARY_CHECKSUM works
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70832

N 56°04'39.26"
E 12°55'05.63"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-05 : 09:29:37

Thanks Peso. Very interesting.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources