• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2042
  • Last Modified:

Cross Apply with Delete, Deleting over a set of values

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.

2011-05-14
2011-06-27
2011-07-08

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:

DELETE BBB_STAGING_TABLE
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??

FYI:
CREATE TABLE [dbo].[MAX_DATE_FOR_ACCOUNTS](
      [ACCOUNT_NUMBER] [varchar](50) NULL,
      [MAX_DATE] [date] NULL
) ON [PRIMARY]

Thanks!

Cipriano
0
cipriano555
Asked:
cipriano555
1 Solution
 
Scott PletcherSenior DBACommented:
>> need to do this for every account number <<

Just drop the WHERE clause from your DELETE.  You don't need a CROSS APPLY for this.
0
 
ThomasianCommented:
You don't really need the MAX_DATE_FOR_ACCOUNTS table. You can query the max date of each account on your delete query
DECLARE @BBB_STAGING_TABLE TABLE (Account_Number varchar(10), My_Date datetime)
INSERT @BBB_STAGING_TABLE
SELECT '7000', '2011-05-14'
UNION ALL SELECT '7000', '2011-06-27'
UNION ALL SELECT '7000', '2011-07-08'
UNION ALL SELECT '8000', '2012-12-24'
UNION ALL SELECT '8000', '2012-09-07'
UNION ALL SELECT '8000', '2012-01-11'

DELETE B
FROM
	(SELECT *, MAX(My_Date) OVER (PARTITION BY Account_Number) MD
	FROM @BBB_STAGING_TABLE B
	) B
WHERE My_Date < MD

SELECT * FROM @BBB_STAGING_TABLE
/*Account_Number	My_Date
7000	2011-07-08 00:00:00.000
8000	2012-12-24 00:00:00.000
*/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now