Majority logic (2 of 3 must agree)

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 2005 Forums

Transact-SQL (2005)

Majority logic (2 of 3 must agree)

Author

Topic

dmenne
Starting Member

5 Posts

Posted - 2009-02-26 : 11:43:38

Hello,

I have to implement a majority logic selection: data are questionnaire responses with 3 records generated by creators 1,2,3 each for one questionaire page (PatID). I want to find those PatID, where at least 2 out of 3 in (a,b,c) have the same value. In the example below, this is the case.

PatID creator a b c
S2000 1 1 1 0
S2000 2 1 0 1
S2000 3 1 1 1

There is a catch: Values can be NULL (=missing), and agreement on NULL is also valid.

My current implementation is below; it seems to work for non-nulls, but fails for nulls. In my real application, there can be 50+ data columns per page, but it would be possible to generate the query once in advance by code when it is highly repetitive.

Any elegant solution?

Dieter


CREATE TABLE [dbo].[test](
	[PatID] [nchar](10) NOT NULL,
	[creator] [int] NOT NULL,
	[a] [int] NULL,
	[b] [int] NULL,
	[c] [int] NULL,
 CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
(
	[PatID] ASC,
	[creator] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2000     ', 1, 1, 1, 0)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2000     ', 2, 1, 0, 1)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2000     ', 3, 1, 1, 1)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2001     ', 1, 2, 2, 0)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2001     ', 2, 2, 0, 3)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2001     ', 3, 2, 2, 3)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2002     ', 1, 4, 4, 0)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2002     ', 2, 3, 4, NULL)
INSERT [dbo].[test] ([PatID], [creator], [a], [b], [c]) VALUES (N'S2002     ', 3, 4, 4, NULL)


--- query
select t1.PatID,t1.a,t1.b,t1.c,  t2.a,t2.b,t2.c, t3.a,t3.b,t3.c  from test as t1 
inner join test as t2 on t1.PatID= t2.PatID
inner join test as t3 on t1.PatID= t3.PatID
where t1.creator = 1 and t2.creator = 2 and t3.creator = 3
and (t1.a=t2.a or t2.a=t3.a or t1.a=t3.a)
and (t1.b=t2.b or t2.b=t3.b or t1.b=t3.b)
and (t1.c=t2.c or t2.c=t3.c or t1.c=t3.c)

Dieter Menne
Menne Biomed Consulting
Tübingen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-26 : 12:11:27

Redesign your table to have one response on each row. Then you can get your answers with simple grouping queries.

PatID	creator	question_no	answer

CODO ERGO SUM

dmenne
Starting Member

5 Posts

Posted - 2009-02-26 : 12:16:12

quote:
Originally posted by Michael Valentine Jones

Redesign your table to have one response on each row. Then you can get your answers with simple grouping queries.
PatID creator question_no answer

CODO ERGO SUM

Sorry, cannot. The design of the tables have been fixed since years, and in the real app there are dates, strings mixed.

Dieter

Dieter Menne
Menne Biomed Consulting
Tübingen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-02-26 : 12:42:50

quote:
Originally posted by dmenne
Any elegant solution?

quote:
Originally posted by dmenne

quote:
Originally posted by Michael Valentine Jones

Redesign your table to have one response on each row. Then you can get your answers with simple grouping queries.
PatID creator question_no answer

CODO ERGO SUM

Sorry, cannot. The design of the tables have been fixed since years, and in the real app there are dates, strings mixed.

So much for the elegant solution, I guess.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-26 : 12:50:11

If you want to use the solution you currently have, and your problem is simply catching the NULL values, you can just check for possible NULLs in your comparison, like this:

--- query
select t1.PatID,t1.a,t1.b,t1.c, t2.a,t2.b,t2.c, t3.a,t3.b,t3.c
from test as t1
inner join test as t2 on t1.PatID= t2.PatID
inner join test as t3 on t1.PatID= t3.PatID
where t1.creator = 1 and t2.creator = 2 and t3.creator = 3
and (isnull(t1.a,'')=isnull(t2.a,'') or isnull(t2.a=t3.a,'') or isnull(t1.a,'')=isnull(t3.a,''))
and (isnull(t1.b,'')=isnull(t2.b,'') or isnull(t2.b=t3.b,'') or isnull(t1.b,'')=isnull(t3.b,''))
and (isnull(t1.c,'')=isnull(t2.c,'') or isnull(t2.c=t3.c,'') or isnull(t1.c,'')=isnull(t3.c,''))

--OR--
or you could put the isnull() check elsewhere like this:

select t1.PatID,t1.a,t1.b,t1.c, t2.a,t2.b,t2.c, t3.a,t3.b,t3.c
from (select patid, creator, isnull(a,'') as a, isnull(b,'') as b, isnull(c,'') as c from test) as t1
inner join (select patid, creator, isnull(a,'') as a, isnull(b,'') as b, isnull(c,'') as c from test) as t2 on t1.PatID= t2.PatID
inner join (select patid, creator, isnull(a,'') as a, isnull(b,'') as b, isnull(c,'') as c from test) as t3 on t1.PatID= t3.PatID
where t1.creator = 1 and t2.creator = 2 and t3.creator = 3
and (t1.a=t2.a or t2.a=t3.a or t1.a=t3.a)
and (t1.b=t2.b or t2.b=t3.b or t1.b=t3.b)
and (t1.c=t2.c or t2.c=t3.c or t1.c=t3.c)

Someone else may come up with a more radical change for you, but this would work for just the problem of comparing NULLs.

dmenne
Starting Member

5 Posts

Posted - 2009-02-26 : 13:00:18

quote:
Originally posted by SQLforGirls

If you want to use the solution you currently have, and your problem is simply catching the NULL values, you can just check for possible NULLs in your comparison, like this:

--- query
select t1.PatID,t1.a,t1.b,t1.c, t2.a,t2.b,t2.c, t3.a,t3.b,t3.c
from test as t1
inner join test as t2 on t1.PatID= t2.PatID
inner join test as t3 on t1.PatID= t3.PatID
where t1.creator = 1 and t2.creator = 2 and t3.creator = 3
....

It's a horrible approach, isn't it? Michael's idea brought me to this alternative: Create the melted (term stolen from Hadley Wickham) table with checksums on the fly and use grouping. It's not a problem, because due to good preselection there will be never more than 1-10 cases where majority logic is required, so I have a very short candidate list. And yes, I am aware of the limitations of CHECKSUM, but it's even acceptable to have very rare missed cases. Maybe I could also convert to strings instead.


create table #colchecks(PatID nvarchar(6) not null,creator int,col nvarchar(20),checks int )
  
INSERT INTO #colchecks SELECT PatID, creator,'a' as col,CHECKSUM(a) as checks from test
INSERT INTO #colchecks SELECT PatID, creator,'b' as col,CHECKSUM(b) as checks from test
INSERT INTO #colchecks SELECT PatID, creator,'c' as col,CHECKSUM(c) as checks from test

Dieter Menne
Menne Biomed Consulting
Tübingen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-26 : 13:55:24

Used UNPIVOT?

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

dmenne
Starting Member

5 Posts

Posted - 2009-02-26 : 14:01:55

[quote]Originally posted by Peso

Used UNPIVOT?

Did not know this one exists... thanks

Dieter

Dieter Menne
Menne Biomed Consulting
Tübingen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources