I have a table that has multiple rows of data for each account number.
The rows of data include a date as well as other data.
I need to delete, for every account, all rows whose date is less than the maximum date found for data for that account.
Example: suppose account 7000 has 10 rows of data, and among those 10 rows, we find three distinct dates.
Any row that has the dates 2011-05-14 or 2011-06-27 and the account 7000 must be eliminated.
I have a table, MAX_DATE_FOR_ACCOUNTS, that gives the maximum date for each account.
The data for account 7000 can be eliminated like this:
FROM BBB_STAGING_TABLE AS STAGE
INNER JOIN MAX_DATE_FOR_ACCOUNTS AS MAXDATE
ON STAGE.ACCOUNT_NUMBER = MAXDATE.ACCOUNT_NUMBER
WHERE STAGE.ACCOUNT_NUMBER = '7000'
AND MY_Date < [MAX_DATE]
Note, THE VALUE OF MAX_DATE IS A FUNCTION OF the account number, in this case, 7000.
I need to do this for every account number. Is there a way using CROSS APPLY?
If not, what is the best way to do this??
CREATE TABLE [dbo].[MAX_DATE_FOR_ACCOUNTS](
[ACCOUNT_NUMBER] [varchar](50) NULL,
[MAX_DATE] [date] NULL
) ON [PRIMARY]