CREATE VIEW [dbo].[MISTableViewWithTime] AS
/*
  Created by Kent Johansen, December 2024
  Changes:
*/
SELECT TOP 100 PERCENT MISTable.Room_HallType, MISTable.DateStamp, MISTable.SegmentNumber, MISTable.PointOfSaleNumber, 
MISTable.RoomTypeNumber, MISTable.ActualAdult, MISTable.ActualChildren1, MISTable.ActualChildren2, MISTable.ActualNumberOfRooms, 
MISTable.BookedAdult, MISTable.BookedChildren1, MISTable.BookedChildren2, MISTable.BookedNumberOfRooms, MISTable.ValueType, 
MISTable.ReadOnly, MISTable.HotelCustomerNumber, MISTable.ReservationStatus, MISTable.StaffID, MISTable.CampaignCode, 
MISTable.ProjectCode, MISTable.AccountNumber, MISTable.MISData, MISTable.DepartmentNumber, MISTable.ProductWareLinkNumber, 
MISTable.ReferenceNumber, MISTable.FolioNumber, MISTable.GuestNumber, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0) END AS ActualPriceInclPackage, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE (MISTable.ActualPrice + ISNULL(MISPackagesSum_1.PackageSum, 0)) - (MISTable.ActualVatOfPrice + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0)) END AS ActualPriceInclPackageExVat, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice END AS ActualPriceExPackage, 
CASE WHEN MISTable.DateStamp >= System.SystemDate THEN 0 ELSE MISTable.ActualPrice - MISTable.ActualVatOfPrice END AS ActualPriceExPackageExVat,
MISTable.ActualPackage AS ActualOnlyPackage, 
MISTable.ActualPackage - MISTable.ActualVatOfPackage AS ActualOnlyPackageExVat, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice END AS BookedPriceInclPackage, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - MISTable.BookedVatOfPrice END AS BookedPriceInclPackageExVat,
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) END AS BookedPriceExPackage, 
CASE WHEN MISTable.DateStamp < System.SystemDate THEN 0 ELSE MISTable.BookedPrice - MISTable.BookedVatOfPrice - ISNULL(MISPackagesSum_1.PackageSum, 0) + ISNULL(MISPackagesSum_1.PackageVatOfSum, 0) END AS BookedPriceExPackageExVat, 
MISTable.BookedPackage AS BookedOnlyPackage, 
MISTable.BookedPackage - MISTable.BookedVatOfPackage AS BookedOnlyPackageExVat, 
MISTable.OwnerReservation, MISTable.IsPackage, MISTable.CorrectionMoved, MISTable.CorrectionDeleted, MISTable.Text, MISTable.WareGroupNumber,
MISTable.ProductNumber, MISTable.WareType, MISTable.WareNumber,MISTable.ActualWareCount, MISTable.BookedWareCount, MISTable.FromTime, MISTable.ToTime
FROM  MISTable WITH (NOLOCK) 
LEFT OUTER JOIN MISTablePackSum AS MISPackagesSum_1 ON MISTable.ProductWareLinkNumber = MISPackagesSum_1.ProductWareLinkNumber 
  AND MISTable.IsPackage = 0 
LEFT OUTER JOIN System ON 1 = 1
WHERE (MISTable.ReservationStatus NOT IN (7,8) OR MISTable.FolioNumber <> -1)
ORDER BY MISTable.ProductWareLinkNumber
GO


