oldest account by store

[nickdaniels]

I need a query which will retrieve the oldest account number for each store, showing the account number, the date the account was established and the store code. The code below is close, but brings back every account number for each store and assigns it the same (oldest) established date. Does anyone have any suggestions?

cheers, Nick

SELECT
to_char(ACCOUNT.ACCOUNT_NUMBER),
ACCOUNT.STORE_CODE,
Oldest_Account.ACCOUNT_ESTABLISHED_DATE
FROM
ACCOUNT,
ACCOUNT Oldest_Account
WHERE
( ACCOUNT.STORE_CODE=Oldest_Account.STORE_CODE )
AND ( Oldest_Account.ACCOUNT_ESTABLISHED_DATE = (select min(account_established_date) from account
where account.STORE_CODE =Oldest_Account.STORE_CODE)
)


Nick
ndaniels@ventura-uk.com

If it's Data Warehousing or related then I want to know - but post it to the forum!

 

[THoey]

I ran this in Access and it works, but depending on what flavor of SQL you are using, you will have to tweak it...

[b]
SELECT DISTINCT Store.Store_Code, First(Store.Account_Number) AS FirstOfAccount_Number, Min(Store.Account_Established_Date) AS MinOfAccount_Established_Date
FROM Store
GROUP BY Store.Store_Code;
[/b]

Hope it helps...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[nickdaniels]

In the end I added the established date from account to the select and within the report (business objects) compare the two dates and where they match just display those rows. It runs like a dog, but hey! its a start. thanks anyway

Nick
ndaniels@ventura-uk.com

If it's Data Warehousing or related then I want to know - but post it to the forum!