CREATE PROCEDURE DBO.DeleteInvalidCustomers AS
/* Created february 2011 by Villy Damsbo */

DECLARE
@CustomerFieldsPerHotelDeleted INT,
@CustomerDeleted INT,
@StartDate DATETIME

SET @StartDate = GetDate()

DELETE FROM CustomerFieldsPerHotel
WHERE
  (CustomerNumber IN (SELECT CustomerNumber 
                      FROM Customer WITH (NOLOCK)
                      WHERE 
                        ISNULL(LastName, '') = '' AND 
                        MarkAsDeleted = 1)) AND
  (CustomerNumber NOT IN (SELECT Link FROM ReservationAddress WITH (NOLOCK))) AND
  (CustomerNumber NOT IN (SELECT ContactpersonCustomerNumber FROM ReservationAddress WITH (NOLOCK)))
    
SET @CustomerFieldsPerHotelDeleted = @@ROWCOUNT 

DELETE FROM Customer
WHERE 
 (ISNULL(LastName, '') = '' AND MarkAsDeleted = 1) AND
  (CustomerNumber NOT IN (SELECT Link FROM ReservationAddress WITH (NOLOCK))) AND
  (CustomerNumber NOT IN (SELECT ContactpersonCustomerNumber FROM ReservationAddress WITH (NOLOCK)))
SET @CustomerDeleted = @@ROWCOUNT

SELECT
  @CustomerDeleted AS Deleted, 
  @CustomerFieldsPerHotelDeleted + @CustomerDeleted AS TotalRowsAffected, 
  GetDate() - @StartDate AS TimeSpent

