CREATE PROCEDURE [dbo].[DeleteOldCustomer] AS
/*
  NB20051101 Created
  Delete all old customers before the date specified in hotelsetup for customers 
  Parameters @DeletedFrom is the same as TProgramPart in Types_Constants.pas
  Notes
  Requirements:
  1. do not delete sales channel type (7) customers
  2. Customers not related to the bonuscard system
  3. Created before a specified date
  4. With no turnover after a (nother?) specified dat. Remember to delete the link between the customer and reservation (ReservationAddress) on the matching customer records by the turnover criteria

  
*/

DELETE FROM Customer  
WHERE CustomerTypeNumber = 1 AND CustomerNumber NOT IN ( 
  SELECT DISTINCT C.CustomerNumber FROM Customer C WITH (NOLOCK) INNER JOIN 
    CustomerOrdreBook COB WITH (NOLOCK) ON COB.CustomerNumber=C.CustomerNumber AND C.CustomerTypeNumber=1
  WHERE ISNULL(COB.TurnoverRooms, 0) > 0 OR ISNULL(COB.TurnoverDayMeetings, 0) > 0 OR ISNULL(COB.TurnoverRooms, 0) > 0 OR ISNULL(COB.TurnoverRestaurant, 0) > 0
  )
  AND CustomerNumber NOT IN (SELECT CustomerNumber FROM Customer WITH (NOLOCK) 
    WHERE EstablishDate >= '20040101' OR (EstablishDate IS NULL AND MarkAsDeleted = 0)
  )

