Picking records having both of two criteria

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)

Picking records having both of two criteria

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-05-14 : 09:37:42

Hi,

Slightly knotty and hard-to-articulate problem here, so please bear with me.

As part of a series of transactions, I have a table which contains data which looks like this:


AdBookingID adWeeks     AdID        clientId    valueid     
----------- ----------- ----------- ----------- ----------- 
97336       3           95127       248         1007
98220       1           94304       458         1007
98220       1           94304       458         7610
98386       1           88123       319         7604
98388       1           91484       319         7610
98390       1           91963       319         7610
98392       1           92468       319         7601
98392       1           92468       319         7608

The key to this problem is the "valueID". The first digit of the value is significant - the "1" and the "7" in the above mean the values are parts of different groups of search criteria.

What I need to do is to select from this list into a temp table all those bookings (identified by AdBookingID) which have valueIds of both types - i.e. an row in the table where the valueId starts with a 1 and a row in the table where the valueId starts with a 7 - in the above data sample the only AdBooking which qualifies is 98220.

I worked out a method of doing this going by the number of entries each item had in the table:


select count(distinct valueId) 
    as valueId, adWeeks, clientId, adID, AdBookingID 
    into #worktable 
    from #tmp 
    group by adWeeks, clientId, adID, AdBookingID 
    having count(distinct valueid) > 1

Which is largely accurate. But - and here's the killer - although each adBooking can only have one valueId starting with 1, it can have multiple entries starting with 7. These bookings are rare, but they do exist and are causing anomalies in the data returned which - if you recall - must only contain booking records for which there are valueId entries with both a 1 and a 7.

Can anyone suggest a way I can get just the data I need?
Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 09:50:35

[code]SELECT AdBookingID
FROM Table
WHERE ((valueid/1000)=1 or (valueid/1000)=7)
GROUP BY AdBookingID
HAVING COUNT(DISTINCT (value/1000))=2[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-14 : 09:56:20

Damn you visakh!

Here's what I did, which is along the same lines...

-- Structure and data
declare @t table (AdBookingID int, adWeeks int, AdID int, clientId int, valueid int)
insert @t
          select 97336, 3, 95127, 248, 1007
union all select 98220, 1, 94304, 458, 1007
union all select 98220, 1, 94304, 458, 7610
union all select 98386, 1, 88123, 319, 7604
union all select 98388, 1, 91484, 319, 7610
union all select 98390, 1, 91963, 319, 7610
union all select 98392, 1, 92468, 319, 7601
union all select 98392, 1, 92468, 319, 7608

-- Calculation
select * from @t where AdBookingID in (select AdBookingID from @t group by AdBookingID
    having count(distinct valueid / 1000) = 2)

/* Results
AdBookingID adWeeks     AdID        clientId    valueid
----------- ----------- ----------- ----------- -----------
98220       1           94304       458         1007
98220       1           94304       458         7610
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

mattt
Posting Yak Master

194 Posts

Posted - 2008-05-14 : 10:02:59

Thanks guys, cunning approach there.

I've plugged your suggestions into the acutal procedure and it's producing results much closer to those I'd expect. I need to dig a bit further to determine if they're 100% accurate though so I may be back with further questions :)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-14 : 10:06:30

If valueid is not just in the thousands, I guess you'll need to use "left(cast(valueid as varchar(10)), 1)" rather than "valueid / 1000".

Since the 1 and the 7 have meaning, you could consider adding a new column to represent that - either physically, or as a computed column...

alter table myTable add groupid as valueid / 1000

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:08:20

quote:

Originally posted by RyanRandall

Damn you visakh!

Here's what I did, which is along the same lines...

-- Structure and data
declare @t table (AdBookingID int, adWeeks int, AdID int, clientId int, valueid int)
insert @t
          select 97336, 3, 95127, 248, 1007
union all select 98220, 1, 94304, 458, 1007
union all select 98220, 1, 94304, 458, 7610
union all select 98386, 1, 88123, 319, 7604
union all select 98388, 1, 91484, 319, 7610
union all select 98390, 1, 91963, 319, 7610
union all select 98392, 1, 92468, 319, 7601
union all select 98392, 1, 92468, 319, 7608

-- Calculation
select * from @t where AdBookingID in (select AdBookingID from @t group by AdBookingID
    having count(distinct valueid / 1000) = 2)

/* Results
AdBookingID adWeeks     AdID        clientId    valueid
----------- ----------- ----------- ----------- -----------
98220       1           94304       458         1007
98220       1           94304       458         7610
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Great souls think alike

mattt
Posting Yak Master

194 Posts

Posted - 2008-05-14 : 10:22:22

Ok, looking pretty good so far. I've found one anomaly though - any idea why the query


SELECT AdBookingID, clientId, adWeeks into #worktable
FROM #tmp
WHERE ((valueid/1000)=1 or (valueid/1000)=7)
GROUP BY AdBookingID, clientId, AdWeeks
HAVING COUNT(DISTINCT (valueid/1000))=2

Would let this record through?


AdBookingID adWeeks     AdID        clientId    valueid     
----------- ----------- ----------- ----------- ----------- 
103099       1           97176       8673         7608
103099       1           97176       8673         7610
103099       1           97176       8673         7612

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 10:43:21

quote:

Originally posted by mattt

Ok, looking pretty good so far. I've found one anomaly though - any idea why the query


SELECT AdBookingID, clientId, adWeeks into #worktable
FROM #tmp
WHERE ((valueid/1000)=1 or (valueid/1000)=7)
GROUP BY AdBookingID, clientId, AdWeeks
HAVING COUNT(DISTINCT (valueid/1000))=2

Would let this record through?


AdBookingID adWeeks     AdID        clientId    valueid     
----------- ----------- ----------- ----------- ----------- 
103099       1           97176       8673         7608
103099       1           97176       8673         7610
103099       1           97176       8673         7612

Cheers,
Matt

It shouldnt let this as per logic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources