May 10, 2004 at 4:53 pm
Hello all,
A little question regarding SQL Server DB's.
I have a two tables containing customers invoices, one for the invoices header (ie: customer #, invoice date,... KEY: invoice # + invoice date) and another for the details of the invoices (ie: each invoice line details KEY: invoice # + line #). I need to periodically remove invoices older than a certain timeframe (ex: all invoices older than 48 months).
How can I proceed?
I am fairly new with SQL server.... Please help!
Thanks,
Eric
May 10, 2004 at 10:52 pm
Hi!
You can write the delete statement based on need i.e. where the difference of invoice date and current date is greater than equal to 48 months. This delete statement can be scheduled as a job that can run on the time given for eg. daily/ weekly or whatever frequency you specify. Job can be scheduled using enterprise manager or thru code too.
Regards
Indu
Indu Jakhar
May 10, 2004 at 11:06 pm
In the interests of referential integrity, you'll probably need two delete statements. The first will delete the relevant invoice lines and the second their corresponding header records.
(Not sure quite why you would delete an invoice, but here goes...)
Your statements will look something like this:
delete il
from
invlines il inner join invhdr ih on il.invno = ih.invno
where ih.invdate < (GetDate() - 90)
delete ih
from
invhdr ih
where ih.invdate < (GetDate() - 90)
You might want to perform the deletes as part of a transaction to ensure that you never find yourself in a situation where there are some invoice headers existing whose lines have been deleted.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 11, 2004 at 7:03 am
Eric,
I agree with Indu and Phil. I, too, am fairly new to SQL. To be on the safe side, I always use a SELECT statement first to verify that the data I'm deleting is correct. I always test a couple of times before performing any delete on a production server. Also - make a backup of the database prior to deleting.
May 11, 2004 at 8:51 am
Actually I would consider an Archive table
Select ? Into Archive_Inv_DeT WHERE ...
Select ? Into Archive_Inv_Hdr ...
Then Delete
For good luck put a marker in the Archive table indicating the date transfered.
KlK
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply