--Create needed fields and update with the record user first valid department relation
--rollback tran
--begin tran
IF (SELECT Col_Length('Invoices', 'FkDepartment')) IS NULL
  ALTER TABLE dbo.Invoices ADD FkDepartment smallint NULL
IF (SELECT Col_Length('CanceledBillNumbers', 'FkDepartment')) IS NULL
  ALTER TABLE dbo.CanceledBillNumbers ADD FkDepartment smallint NULL
go
update dbo.Invoices set FkDepartment= 
  (select top 1 FK_Department from PersonelDepartmentsXtab PDX WITH (nolock) 
    inner join Departments D WITH (nolock) on D.Pk_Department = PDX.Fk_Department
  where PDX.UserIdNumber = Invoices.UserIdNumber and not PDX.FK_Department is null
  order by D.DepartmentNumber)
where FkDepartment is null

update CanceledBillNumbers set FkDepartment= 
  (select top 1 FK_Department from PersonelDepartmentsXtab PDX WITH (nolock) 
    inner join Departments D WITH (nolock) on D.Pk_Department = PDX.Fk_Department
  where PDX.UserIdNumber = CanceledBillNumbers.UserIdNumber and not PDX.FK_Department is null
  order by D.DepartmentNumber)
where FkDepartment is null
--delete if no transactions found with that BillNumber on
delete invoices where FkDepartment is null and BillNumber not in (
select distinct InvoiceBillNumber from transactions tr WITH (nolock) 
  inner join invoices i WITH (nolock) on tr.InvoicebillNumber = i.BillNumber)
--domino
update Invoices set FkDepartment = (select top 1 PK_Department from Departments WITH (nolock) 
  inner join customer WITH (nolock) on customer.HotelNumber = Departments.HotelNumber
  where Invoices.HotelCustomerNumber = customer.CustomerNumber
  order by DepartmentNumber)
where FkDepartment is null
--single
update Invoices set FkDepartment = (select top 1 PK_Department from Departments WITH (nolock) 
  order by DepartmentNumber)
where FkDepartment is null