sql code

Using sql, how would i check to see if a firm has multiple auditors in a certain period, say 2004 - 2007?

I have firm number, fiscal year, and auditor key.  If there are more than one auditor key during the time from 2004-2007 I need to delete the firm.

Thank you!
stephaniem712Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
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

Open in new window

0
stephaniem712Author Commented:
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
0
chaauCommented:
OK, I see. Then the query will be simpler:
SELECT distinct firmNumber
FROM firms
WHERE fiscalYear >= 2004 and fiscalYear <= 2007 and AuditorKey > 1

Open 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)

Open in new window

(Please run the delete statement after you are absolutely sure you want to delete them)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

stephaniem712Author Commented:
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.
0
stephaniem712Author Commented:
This is what I did and it works.  I then delete anything with an audit count greater than 1.

I used your ideas chaau to figure it out.  How do I give you some credit?

proc sql;
      create table bhmrep.auditsox4042 as select *, count(distinct auditor_fkey) as auditcount
      from bhmrep.auditsox4041
      group by company_fkey;
quit;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
Glad yuo have managed to figure it out. Just closing your question and mark your answer as an answer
0
stephaniem712Author Commented:
Tested my code and it worked.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.