Stvjston
3/27/2007 11:13:00 PM
Found a solution...
Posting to close the thread.
SELECT a.*, y.*
FROM Account AS a INNER JOIN (SELECT DISTINCT AccountUnique
FROM Opportunities
WHERE NOT EXISTS (Select distinct b.AccountUnique,MIN(OpportunityDate)
from Opportunities b
where b.AccountUnique = o.AccountUnique
and year(b.OpportunityDate) <> 2007 group by AccountUnique)) AS y ON
y.AccountUnique = a.AccountUnique
"StvJston" wrote:
> Thanks in advance for help received.
>
> Accounts can be created any time
> Accounts can go years before an opportunity is created
> Accounts can have multiple opportunities in a year
>
> Any Account that has an year(opportunityDate) = 2007 but does NOT have any
> previous years opportunities
>
>
> Create Table Account
> (AccountID Varchar(10),
> AccountCreated DateTime)
>
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct01', '01/01/2005')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct02', '01/01/2005')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct03', '01/01/2006')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct04', '01/01/2006')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct05', '01/01/2004')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct06', '01/01/2007')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct07', '01/01/2007')
> INSERT INTO Account(AccountID, AccountCreated) Values('Acct08', '01/01/2005')
>
> Create Table Opportunities
> (AccountID Varchar(10),
> OpportunityDate Datetime)
>
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct01','03/22/2005')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct01', '08/22/2005')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct01','06/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct02', '08/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct03', '04/15/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct04', '10/22/2006')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct04', '08/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct05', '08/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct06', '04/01/2005')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct06', '06/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct06','08/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct08', '06/22/2007')
> INSERT INTO Opportunities(AccountID,Opportunity, OpportunitiyDate) Values
> ('Acct08','08/22/2007')
>
> Results
> Acct02, 08/22/2007
> Acct03, 04/15/2007
> Acct05, 08/22/2007
> Acct08, 06/22/2007
> Acct08, 08/22/2007
>