Crazy Requirement..Tough Query?!

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)

Crazy Requirement..Tough Query?!

Author

Topic

sviking
Starting Member

5 Posts

Posted - 2011-07-20 : 02:36:17

Hello,

I am trying to figure out on how to achieve results for this data

declare @ucm table (custid int, groupno int)
declare @pcm table (pid int, custid int, groupno int)

insert into @ucm (custid, groupno) values (1194, 1)
insert into @ucm (custid, groupno) values (1195, 1)
insert into @ucm (custid, groupno) values (1197, 2)
insert into @ucm (custid, groupno) values (1195, 2)
/*
#1. 1st 2 rows is set 1
#2. 2nd 2 rows is set 2
*/
select * from @ucm

insert into @pcm (pid, custid, groupno) values (1, 1194, 1)
insert into @pcm (pid, custid, groupno) values (1, 1195, 1)
insert into @pcm (pid, custid, groupno) values (1, 1197, 2)
insert into @pcm (pid, custid, groupno) values (1, 1193, 2)
insert into @pcm (pid, custid, groupno) values (2, 1197, 2)
insert into @pcm (pid, custid, groupno) values (2, 1195, 2)

select * from @pcm

/*
#1. 1st 2 rows should match with set 1, hence the result should be showing pid 1.
#2. 2nd 2 rows shouldn't match with set 1, and these rows don't match with the 2nd set also, because 1193 is odd one out. Hence the pid of this match shouldn't return any pid.
#3. 3rd 2 rows should match with set 2 and the result should be showing pid 2.
#4. set 1 matching shouldn't show promotion id 2, and set 2 shouldn't show pid 1.
*/

At the end I would like to have results

pid
1 /*match should be between 1st set of rows and per above conditions*/
2 /*match should be between 2nd set of rows and per above conditions*/

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-20 : 02:51:32

your description is a bit confusing for me.

- what is "promotion id" ? is it pid ?
- can you be specific what is "2nd 2 rows" ? "3rd 2 rows" ? which record are you referring to ?
- What i the expected result ? just one column of "pid" ?

KH
[spoiler]Time is always against us[/spoiler]

sviking
Starting Member

5 Posts

Posted - 2011-07-20 : 02:58:38

Sorry if I missed something. Quick answers to your question-

1. PromotionId is PId
2. Set of rows
insert into @pcm (pid, custid, groupno) values (1, 1194, 1) -- 1st 2 rows
insert into @pcm (pid, custid, groupno) values (1, 1195, 1) -- 1st 2 rows
insert into @pcm (pid, custid, groupno) values (1, 1197, 2) -- 2nd 2 rows
insert into @pcm (pid, custid, groupno) values (1, 1193, 2) -- 2nd 2 rows
insert into @pcm (pid, custid, groupno) values (2, 1197, 2) -- 3rd 2 rows
insert into @pcm (pid, custid, groupno) values (2, 1195, 2) -- 3rd 2 rows

I would need the pid as the end result if I match between 1st 2 rows of ucm and pcm(should be 1), and if I match between 2nd 2 rows of ucm and 3rd 2 rows of pcm (should be 2). The 2nd 2 rows shouldn't give me results because the combination of (1197,2), (1195,2) of ucm doesn't exist in pcm although there is only (1197,2).

I know this is little convoluted. I shall explain in case the above isn't clear.

Thanks for asking questions and the help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 03:30:11

Also post your expected output.

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

sviking
Starting Member

5 Posts

Posted - 2011-07-20 : 03:33:23

Thanks Peso for responding. I just need the pid for the conditional matches of set of rows from both the tables.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 03:38:25

We are not mind-readers. We need an output to match the data against, since your explanations are at least confusing.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-07-20 : 03:44:22

[code]SELECT DISTINCT p.pid
FROM @pcm AS p
LEFT JOIN @ucm AS u ON u.custid = p.custid
AND u.groupno = p.groupno
GROUP BY p.pid,
p.groupno
HAVING MAX(CASE WHEN u.custid IS NULL THEN 1 ELSE 0 END) = 0[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-20 : 03:49:35

try . . .


; with 
grp as
(
	select	groupno, groupcnt = count(*)
	from	@ucm
	group by groupno
),
match as
(
	select	g.groupno, g.groupcnt, u.custid, p.pid, cnt = count(*) over (partition by p.groupno, p.pid)
	from	grp g
		inner join @ucm u	on	g.groupno	= u.groupno
		inner join @pcm p	on	g.groupno	= p.groupno
					and	u.custid	= p.custid
)
-- Show the matching result
-- Change to SELECT DISTINCT pid if you want want the PID
select	*
from	match
where	groupcnt	= cnt

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-20 : 03:50:56

Argh . . . Peso's much shorter and nicer

KH
[spoiler]Time is always against us[/spoiler]

sviking
Starting Member

5 Posts

Posted - 2011-07-20 : 03:51:41

My apologies for not being quite clear on what I wanted. I had been tearing my hairs for quite sometime, and was lost. In short I would need this. Thanks for your time.

pid custid groupno
== ==== ======
1 1194 1
1 1195 1
2 1197 2
2 1195 2

and the below rows shouldn't show up because there is no match
pid custid groupno
== ==== ======
1 1197 2
1 1193 2

I was going through your earlier query, for some strange reason or unknown reason to me I can't join with groupno. Could there be another way to do it.

thanks a bunch.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-20 : 04:01:13

what do you mean by "I can't join with groupno" ?

KH
[spoiler]Time is always against us[/spoiler]

sviking
Starting Member

5 Posts

Posted - 2011-07-20 : 04:31:17

I have the same question for the dev team folks. I think I figured it out why they don't need join on this groupno. I will try on the solutions provided by you and Peso and shall post the way I will go about it.

Thanks for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources