Removing entries with too many matching columns

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)

Removing entries with too many matching columns

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-22 : 19:36:32

I have a table with roughly 150,000 entries in the form of the following

stocknumber price dateacq datesold id_num
01407535 9969 2008-08-28 2008-08-28 28055
01407538 7235 2008-08-28 2008-09-14 89503
01407542 7325 2008-08-28 2008-09-17 41955
01407554 6886 2008-08-28 2008-09-17 84846
01407556 3992 2008-08-28 2008-09-06 92534
01407557 7163 2008-08-28 2008-09-17 10024
01407558 4180 2008-08-28 2008-09-17 28293
01407563 4195 2008-08-28 2008-09-17 82571
01423568 3256 2008-08-29 2008-09-21 35675
01254698 9856 2008-08-29 2008-09-21 57877
01446454 8456 2008-08-28 2008-09-17 84655
01465654 5231 2008-08-29 2008-09-21 43456
01498465 8565 2008-08-29 2008-09-21 67857
01421984 1236 2008-08-29 2008-09-21 45675
01465498 6545 2008-08-28 2008-09-14 56767

I want to write a query that won't display entries that have a specific combination of dateacq and datesold more than 5 times.

for example the combination with dateacq of '2008-08-28' and datesold of '2008-09-17' appears 6 times, so i want to remove it from the result set. The combination of dateacq of '2008-08-29' and datesold of '2008-09-12' appears 5 times, so remove that as well.

The result set should be

stocknumber price dateacq datesold id_num
01407535 9969 2008-08-28 2008-08-28 28055
01407538 7235 2008-08-28 2008-09-14 89503
01407556 3992 2008-08-28 2008-09-06 92534
01465498 6545 2008-08-28 2008-09-14 56767

I started the query by doing this


select stocknumber, price,
dateadd(dd, datediff(dd, 0, first_occurrence), 0) dateacq, dateadd(dd,
datediff(dd, 0, last_occurence), 0) datesold,
max(id_num) as id_num
from bluenile_inventory
group by stocknumber, price, dateadd(dd, datediff(dd, 0,
first_occurrence), 0), dateadd(dd, datediff(dd, 0, last_occurence),
0)

Not sure where to take it now, any help would be great

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 00:19:52

[code]SELECT t.*
FROM YourTable t
INNER JOIN (SELECT dateacq,datesold,COUNT(stocknumber) AS RecCnt
FROM YourTable
GROUP BY dateacq,datesold)tmp
ON tmp.dateacq=t.dateacq
AND tmp.datesold=t.datesold
WHERE tmp.recCnt >=5[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources