One of our customers asked a very interesting question the other day. They wanted to know the top 5 contacts in each country as measured by the number of incoming emails so that they could send an email to each of them.

The answer needs a couple of different elements to make it work:

1. Firstly, a view has to be set up on the SQL server. The script for this is:


use GoldMine

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[vHistoryCount]') AND type in (N'V'))
DROP VIEW [dbo].[vHistoryCount]
GO

create view vHistoryCount with schemabinding
as

select
country as grouping, COMPANY, contact, contact1.accountno, count_big(*) as tally
from
dbo.contact1, dbo.conthist
where
contact1.accountno=conthist.accountno and
userid='rob' and
rectype='mi' and
contact>''
group by country, company, contact, contact1.accountno

GO

CREATE unique clustered index accountno on vHistoryCount (accountno)
CREATE INDEX grouping ON vHistoryCount (grouping, tally, accountno)


This will create a virtual table that contains the country, the company and contact names, the GM accountno and the number of associated history items of type ’email in’ with the userid ‘ROB’. You can obviously tailor this for the type of activity that you are interested in, and you can remove the userid= clause if you don’t want it to be limited to a particular user.

It will work just as well with ‘city’ (or, indeed, ‘key1’ or any other field) instead of ‘country’. Just make a change in two places: the ‘select’ statement and the ‘group by’ statement.

I’m disregarding records with no contact name but you can remove this if you want to.

Once the view has been created then you can query it with ‘select * from vHistoryCount’ to see what’s in there. One thing is that the view uses COUNT_BIG instead of COUNT to keep the indexing happy, but this will display as a blank column in a GoldMine SQL query (it displays fine in Management Studio). You can get round this by using ‘select *, convert(int, tally) as show_tally from vHistoryCount’ instead.

2. Step 2 is to run this SQL query in GoldMine:


SELECT *, CONVERT(int, tally) as real_tally
FROM vHistoryCount h1
WHERE accountno IN ( SELECT TOP(5) accountno FROM vHistoryCount h2 WHERE h1.grouping = h2.grouping ORDER BY tally DESC )
and tally > 10
ORDER BY grouping, tally DESC


This does the clever stuff of seeing which 5 records in each country have the most emails against them. You can change the number of records returned by changing the number in the TOP() statement.

The ‘and tally > 10’ clause lets you apply a minimum number of emails (or whatever your chosen activity is), below which a record doesn’t count.

I would love to be able to claim the credit for this ingenious bit of code but it does rightfully belong to another. I found it in a comment by a chap called Jason Rushton, with optimisation by Rick Osborne, half way down this page: http://www.bennadel.com/blog/1114-selecting-top-x-from-each-group.htm.

The indexing means that it should run really quickly, even on a large database.

Once you’ve got a set of results, you can then create a group from them and then do an email merge.