CREATE PROCEDURE dbo.CleaningTodoDelete AS
/*
  Created by Kent Johansen December 2012
  Delete cleaning todo older than..
  Changes:
  
*/

DECLARE @sql Nvarchar(4000)
DECLARE @Month INT

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

SET @sql = N' DELETE FollowupRoomsXtab WHERE FK_FollowupNumber IN (
  SELECT FollowupNumber
  FROM FollowUp WITH (NOLOCK)
  WHERE Type = 19
  AND Date <= DATEADD(m, ' + CONVERT(VARCHAR, @Month) + '*-1, GETDATE())
)
 
DELETE PersonalFollowUpXTab WHERE FollowUpNumber IN (
  SELECT FollowupNumber
  FROM FollowUp WITH (NOLOCK)
  WHERE Type = 19
  AND Date <= DATEADD(m, ' + CONVERT(VARCHAR, @Month) + '*-1, GETDATE())
)
DELETE Followup
WHERE Type = 19 -- Cleaning
AND Date <= DATEADD(m, ' + CONVERT(VARCHAR, @Month) + '*-1, GETDATE())
'

EXEC sp_executesql @sql