CREATE PROCEDURE [dbo].[AuditLogDeleteFiscalPrinterSwedenLogs] @ANumberOfRecords INT, @AAffectedRows INT OUTPUT AS
/*
  Created by Kent Johansen October 2012
  Delete audit log related to Fiscal Printer Sweden
  Changes:
    KJ 20160815: Optimized the script
  
*/

DECLARE @Month INT

SELECT @Month = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'AuditLogSwedenMonth'
SET @Month = ISNULL(@Month, 60)

SELECT d.Audit_Log_Data_Id, d.Audit_Log_Transaction_Id
INTO #tmp
FROM Audit_Log_Transactions t WITH (NOLOCK)
INNER JOIN Audit_Log_Data d WITH (NOLOCK) ON d.Audit_Log_Transaction_Id = t.Audit_Log_Transaction_Id
WHERE
(
(t.Table_Name = 'Ware' AND d.Col_Name = 'Price') OR
(t.Table_Name = 'ProductPrice') OR
(t.Table_Name = 'CashBox') OR 
(t.Table_Name = 'Invoices') OR 
(t.Table_Name = 'IdentityFields' AND d.Col_Name = 'ChangeDate' AND d.Key1 = 'CASHBOXREPORTPRINTIDENTITY')
)
AND t.Modified_Date <= DATEADD(m, @Month*-1, GETDATE())
ORDER BY d.Audit_Log_Data_Id

DELETE Audit_Log_Data
WHERE Audit_Log_Data.Audit_Log_Data_Id IN (select Audit_Log_Data_Id from #tmp)

SET @AAffectedRows = @@ROWCOUNT

DELETE Audit_Log_Transactions
WHERE Audit_Log_Transaction_Id IN (select Audit_Log_Transaction_Id from #tmp)
AND Audit_Log_Transaction_Id NOT IN (
  SELECT Audit_Log_Transaction_Id FROM Audit_Log_Data WITH (NOLOCK)
)

Drop table #tmp