How delete old transaccions by year?

You can do this using SQL Queries, but you might want to take a look at Retail Realm's Archiving utility which is all set up to do this for you. Contact your reseller for details or check out

Glenn Adams Tiber Creek C> Hi, I have a 7.0 Gb Database and I want to delete by year the transaccions

Did you ever get an answer for this?

Danny

"Gas28" wrote:

The answer was to contact Retail Realm coz they already have an archiving utility, building a script for archiving is big issue

Give this a look.. and test before you use it... no warranties from me. this did come from M$ a couple of years ago. /* DELETING SALES HISTORY FROM HQ */

--INTRUCTIONS

--===============================================================================--These queries will permanently delete all sales history

-- and related records PRIOR TO the specified Batch Number.

--

--Replace the value for @BatchNumberKeep in the SELECT statement

-- with the lowest Batch Number you wish to keep.

--

--EXAMPLE: SELECT @BatchNumberKeep = 50 -- All records before Batch Number 50 will be deleted

--

--==============================================================================--select max(batchnumber)

--from batch

--where closingtime < dateadd(mm,-7,getdate())

DECLARE @BatchNumberKeep int DECLARE @StoreID int declare @TransDate smalldatetime

SELECT @BatchNumberKeep = 0 SELECT @StoreID = 0

--Select @TransDate = '2003-10-10' -- use this format 'yyyy/mm/dd'

DELETE Shipping FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND Shipping.StoreID = @StoreID AND Shipping.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate

DELETE TransactionEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TransactionEntry.StoreID = @StoreID AND TransactionEntry.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE TaxEntry FROM [Transaction] WHERE BatchNumber < @BatchNumberKeep AND TaxEntry.StoreID = @StoreID AND TaxEntry.TransactionNumber = [Transaction].TransactionNumber --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE SignatureCapture FROM TenderEntry WHERE BatchNumber < @BatchNumberKeep AND SignatureCapture.StoreID = @StoreID AND SignatureCapture.TenderEntryID = TenderEntry.ID

DELETE [Transaction] WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10),[Transaction].Time,111) = @TransDate DELETE Journal WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10), Time,111) = @TransDate

DELETE TaxTotals WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID --and convert(char(10),date,111) = @TransDate

DELETE TenderEntry WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID

DELETE TenderTotals WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID

DELETE Journal WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID

DELETE Batch WHERE BatchNumber < @BatchNumberKeep AND StoreID = @StoreID

Deleting transactions can be dangerous as you may levae behind orphan records. You may want to use the Retail Realm archiving module.

Afshin Alikhani - snipped-for-privacy@retailrealm.co.uk CEO - Retail Realm

--===============================================================================> --These queries will permanently delete all sales history

--===============================================================================>