Deleting Records whose Date is Less than..

Question

Post reply

Deleting Records whose Date is Less than..

Barun Kumar Tiwari

SSC Enthusiast

Points: 159
More actions
September 14, 2006 at 3:54 am

#170351

Hello Everybody,
I am a Beginner in MS-SQL. I am facing a simple problem in deleting some records from a MS-SQL-2000-Server Database. Kindly go through the Contents of the Table and my question::
TABLE NAME: ABCD
ACCTNO LOTDATE       BALANCE
--------------------------------
25         11-12-2005   500
25         13-12-2005   600
25         14-12-2005   450
26         01-05-2005   800
26         05-05-2005   900
26         15-05-2005   700
27         06-09-2005   600
27         08-09-2005   700
27         14-09-2005   800
No Primary or Foreign Key's are Difined.
Please see that the same record (eg: ACCTNO) is getting
repeated. The only difference is in the LOTDATE and
BALANCE.
Kindly give a solution (Script) to delete only those
repeating Records where the LOTDATE is smallest irrespective
of the Balance.
The Result should look like::
ACCTNO   LOTDATE    BALANCE
--------------------------------
25          14-12-2005   450
26          15-05-2005   700
27          14-09-2005   800
Thanks for a reply,
Barun Kumar Tiwari
INDIA

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 1

DELETE ABCD

FROM ABCD

INNER JOIN

(

SELECT ACCTNO,MAX(LOTDATE) AS MAX_DATE

FROM ABCD

GROUP BY ACCTNO

) AS MOST_RECENT

ON MOST_RECENT.ACCTNO=ABCD.ACCTNO

AND ABCD.LOTDATE<MOST_RECENT.MAX_DATE

should do it

Barun Kumar Tiwari SSC Enthusiast Points: 159 More actions · Answer 2

Hello Jo,

Thanks for your reply. The Script u provided worked well. It can delete the required records. But Sir I am facing one more problem:

I need to send the remaining records (after deleting unwanted ones) to MS-Excel Spreadsheet. I am able to send it in CSV format directly from Query Analyser to Excel. But when the Records reach Excel, there the date (lotdat) coloumn shows only 00.00.0. The dates which are visible in the SELECT result set (after Delete) are not showing/coming up as it is. Now dates are visible in excel.

Please give a solution,

With regards, Bye

Barun Kumar Tiwari

INDIA

Jo Pattyn SSC-Dedicated Points: 32473 More actions · Answer 3

Buran Kumar Tiwari,

Can you make a seperate topic of the issue with CSV-Excel, eventually with a link to this post? The logdates showing as 00.00.0, are they displayed as real-dates in query analyzer?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

It's just the excel formatting. Nothing wrong with the data

Highlight the cells, and apply one of the formats under date.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Barun Kumar Tiwari SSC Enthusiast Points: 159 More actions · Answer 5

Hello JO and GilaMonster,

Thanks for your co-operation and extending a helping hand for me. Jo's solution was OK. As GilaMOnster said, the problem was with the Excel Formating. I have changed the formating to dd/mm/yyyy, now everything is just fine.

Thanks.........alot.......Bye,

Barun Kumar Tiwari

INDIA