Truncate Financial Data
Truncate Financial Data
The below script will truncate all financial data to prepare a database for replication.
i.e. If you copy a K1 database from one location to a new server, run this script to clear the location's financial data.
// When running the top box, run the Delete command THEN run the DBCC command.
*** IF you only want to remove a specific date or date range, but want to keep all the date before and after, you can use the following script.
You just need to set @RemoveStartDateTime to be the first date & time you want to remove. Then change @RemoveEndDateTime to be the last date & time you want to be removed. Don't forget to back up the DB first.
USE [ClubspeedV8]
DECLARE @RemoveEndDateTime datetime;
DECLARE @RemoveStartDateTime datetime;
SET @RemoveEndDateTime = '2017-02-24T23:30:00.000';
SET @RemoveStartDateTime = '2017-02-24T04:01:00';
DELETE CD
FROM [ClubspeedV8].[dbo].[Checks] C
INNER JOIN [ClubspeedV8].[dbo].[Checkdetails] CD ON
CD.CheckID = C.CheckID
WHERE C.OpenedDate between @RemoveStartDateTime and @RemoveEndDateTime
DELETE OP
FROM [ClubspeedV8].[dbo].[Checks] C
INNER JOIN [ClubspeedV8].[dbo].[Overpaid] OP ON
OP.CheckID = C.CheckID
WHERE C.OpenedDate between @RemoveStartDateTime and @RemoveEndDateTime
DELETE FROM [ClubspeedV8].[dbo].[Expenses] WHERE [Date] between @RemoveStartDateTime and @RemoveEndDateTime
DELETE FROM [ClubspeedV8].[dbo].[Accounting] WHERE [Date] between @RemoveStartDateTime and @RemoveEndDateTime
DELETE FROM [ClubspeedV8].[dbo].[CashInCashOut] WHERE [Date] between @RemoveStartDateTime and @RemoveEndDateTime
DELETE FROM [ClubspeedV8].[dbo].[Checks] WHERE OpenedDate between @RemoveStartDateTime and @RemoveEndDateTime
DELETE FROM [ClubspeedV8].[dbo].[Payment] WHERE PayDate between @RemoveStartDateTime and @RemoveEndDateTime
*** IF the track is already open and needs to keep some of the Financial, you can run the below script (also attached).
All you have to do is change the @RemoveOnAndBeforeDate to the last day and time you want to delete and it will delete all transactions before & including that date.
USE [ClubspeedV8]
DECLARE @RemoveOnAndBeforeDate datetime;
SET @RemoveOnAndBeforeDate = '2016-04-21T00:00:00.000';
DELETE CD
FROM [ClubspeedV8].[dbo].[Checks] C
INNER JOIN [ClubspeedV8].[dbo].[Checkdetails] CD ON
CD.CheckID = C.CheckID
WHERE C.OpenedDate <= @RemoveOnAndBeforeDate
DELETE OP
FROM [ClubspeedV8].[dbo].[Checks] C
INNER JOIN [ClubspeedV8].[dbo].[Overpaid] OP ON
OP.CheckID = C.CheckID
WHERE C.OpenedDate <= @RemoveOnAndBeforeDate
DELETE FROM [ClubspeedV8].[dbo].[Expenses] WHERE [Date] <= @RemoveOnAndBeforeDate
DELETE FROM [ClubspeedV8].[dbo].[Accounting] WHERE [Date] <= @RemoveOnAndBeforeDate
DELETE FROM [ClubspeedV8].[dbo].[CashInCashOut] WHERE [Date] <= @RemoveOnAndBeforeDate
DELETE FROM [ClubspeedV8].[dbo].[Checks] WHERE OpenedDate <= @RemoveOnAndBeforeDate
DELETE FROM [ClubspeedV8].[dbo].[Payment] WHERE PayDate <= @RemoveOnAndBeforeDate