CREATE VIEW [dbo].[BookkeepingInfoView] AS
SELECT 
Accounts.*,
Folios.FolioNumber, Folios.ReferenceNumber, Folios.GuestNumber,
(SELECT TOP 1 I.BillNumber FROM Invoice I WITH (NOLOCK) 
  INNER JOIN InvoiceTransactionXtab ITX WITH (NOLOCK) ON ITX.FkInvoice = I.PkInvoice
  WHERE ITX.FkTransaction = Transactions.TransactionNumber AND I.CancelDate IS NULL) AS BillNumber,
Transactions.Debit, Transactions.PriceOverwrite AS Price, Transactions.TextOverwrite, Transactions.DateStamp, Transactions.Pieces, Transactions.MovedDeleted, Transactions.MovedFolioNr, Transactions.VATValue, 
Transactions.HotelCustomerNumber, Customer.LastName AS HotelName,
Transactions.UserIdNumber, Personel.UserIDCode,
Transactions.WareNumber, Ware.WareName,
WareGroups.WareGroupNumber AS WareGroupNumberKey, WareGroups.Text AS WareGroupName,
AccountGroups.GroupName AS AccountGroupName, AccountGroups.AccountGroupNumber AS AccountGroupKey, AccountGroups.GroupNumber AS AccountGroupNo, 
TurnOverGroups.Name AS TurnOverGroupName, TurnOverGroups.Number AS TurnoverGroupKey,
Departments.PK_Department AS DepartmentKey, Departments.Name AS DepartmentName
FROM Accounts WITH (NOLOCK)
FULL JOIN TurnOverGroups WITH (NOLOCK) ON TurnoverGroups.Number = Accounts.TurnOverGroupNumber
FULL JOIN AccountGroups WITH (NOLOCK) ON AccountGroups.AccountGroupNumber = Accounts.AccountGroupNumber
FULL JOIN Transactions WITH (NOLOCK) ON Transactions.AccountNumber = Accounts.AccountNumber
INNER JOIN Personel WITH (NOLOCK) ON Transactions.UserIDNumber = Personel.UserIDNumber
INNER JOIN Folios WITH (NOLOCK) ON Transactions.FolioNumber = Folios.FolioNumber
INNER JOIN Ware WITH (NOLOCK) ON Transactions.WareNumber = Ware.WareNumber
INNER JOIN WareGroups WITH (NOLOCK) ON WareGroups.WareGroupNumber = Ware.WareGroupNumber
LEFT JOIN Departments WITH (NOLOCK) ON Departments.PK_Department = Transactions.FK_DepartmentNumber
LEFT OUTER JOIN Customer WITH (NOLOCK) ON Customer.CustomerNumber = Transactions.HotelCustomerNumber