--Fix FK_DepartmentNumber on transactions
--rollback tran
--begin tran
IF (SELECT Col_Length('Transactions', 'Fk_DepartmentNumber')) IS NULL
  ALTER TABLE dbo.Transactions ADD FK_DepartmentNumber smallint NULL
GO
--domino
UPDATE Transactions set DepartmentOverwrite = (
  select top 1 DepartmentNumber from Departments d WITH (nolock) 
    inner join customer c WITH (nolock) on d.HotelNumber = c.HotelNumber
  where c.CustomerNumber = Transactions.HotelCustomerNumber
  order by DepartmentNumber)
where Transactions.DepartmentOverwrite is null
--one hotel
UPDATE Transactions set DepartmentOverwrite = (
  select top 1 DepartmentNumber from Departments d WITH (nolock) 
  order by DepartmentNumber)
where Transactions.DepartmentOverwrite is null

UPDATE Transactions SET FK_DepartmentNumber = D.PK_Department
FROM Transactions, Departments AS D
WHERE Transactions.FK_DepartmentNumber IS NULL AND Transactions.DepartmentOverwrite = D.DepartmentNumber

UPDATE Transactions SET FK_DepartmentNumber = (SELECT TOP 1 D.PK_Department from departments as D WITH (nolock) order by D.DepartmentNumber) 
FROM Transactions
WHERE FK_DepartmentNumber IS NULL
--SELECT * FROM Transactions (NOLOCK) WHERE FK_DepartmentNumber IS NULL

