sql code

You need to use the GROUP BY together with HAVING, like this:

SELECT firmNumber, Count(*)
FROM firms
WHERE fiscalYear >= 2004 and fiscalYear <= 2007
GROUP BY firmNumber
HAVING Count(*) > 1

Select allOpen in new window

Hi,

Thanks, but I am going to have data that looks like this..  What I need to be able to do is determine that firm number 4321 has multiple auditors or changed auditors during the sample period.  firm number 1234 did not change auditors or have multiple auditors.  If the firm has multiple auditors, I want to delete delete those firms.

Firm Number     Fiscal Year    Auditor Key
1234                      2004                 1
1234                      2005                 1
1234                      2006                 1
4321                      2004                 1
4321                      2005                 1
4321                      2006                 2
4321                      2007                 2

OK, I see. Then the query will be simpler:

SELECT distinct firmNumber
FROM firms
WHERE fiscalYear >= 2004 and fiscalYear <= 2007 and AuditorKey > 1

Select allOpen in new window

To delete the firms run this query:

DELETE FROM firms where firmNumber in(SELECT distinct firmNumber
FROM firms
WHERE fiscalYear >= 2004 and fiscalYear <= 2007 and AuditorKey > 1)

Select allOpen in new window

(Please run the delete statement after you are absolutely sure you want to delete them)

Sorry,

I should be more clear.  The auditor key identifies each auditor.  So, if there is more than one unique auditor for each firm during my sample period, I need to delete those firms.

So, in my example, firm 1234 is fine because it had only one auditor the whole time -- auditor #1 whereas firm 4321 had two auditors --- auditor #1 and auditor #2.

auditor key #1 could be PriceWaterhouse and auditor key #2 could be KPMG.  If a firm has more than one auditor during my sample period, I need to delete those firms.

Glad yuo have managed to figure it out. Just closing your question and mark your answer as an answer

Tested my code and it worked.