?
Solved

Cross Apply with Delete, Deleting over a set of values

Posted on 2012-09-07
2
Medium Priority
?
1,881 Views
Last Modified: 2012-09-18
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
Comment
Question by:cipriano555
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 38378180
>> 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
 
LVL 22

Expert Comment

by:Thomasian
ID: 38378630
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question