--DROP TABLE [hmsPrs].[FactPropertyCharge]
--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO

--SET ANSI_PADDING ON
--GO

--CREATE TABLE [hmsPrs].[FactPropertyCharge](
--	[PropertyChargeID] [int] IDENTITY(1,1) NOT NULL,
--	[PropertyID] [bigint] NOT NULL,
--	[TransactionTypeID] [INT] NOT NULL,
--	[ChargeEffectiveFromDateID] [int] NOT NULL,
--	[ChargeEffectiveToDateID] [int] NOT NULL,
--	[CreationDateID] [int] NOT NULL,
--	[SetUpOrAmendTimeID] [int] NOT NULL,
--	[CreatedByOfficerID] [int] NOT NULL,
--	[TerminationOfficerID] [int] NOT NULL,
--	[TerminationDateID] [int] NOT NULL,
--	[TerminationSetupTimeID] [int] NOT NULL,
--	[Value] [numeric](17, 2) NULL,
--	[FixedChargeApplicableInd] [bit] NULL,
--	[BackdatedChargesProcessed] [bit] NULL,
--	[PolicyChargeValue] [numeric](17, 2) NULL,
--) ON [PRIMARY]

--GO

--SET ANSI_PADDING OFF
--GO

--INSERT [hmsPrs].[FactPropertyCharge] ([PropertyID]
--      ,[TransactionTypeID]
--      ,[ChargeEffectiveFromDateID]
--      ,[ChargeEffectiveToDateID]
--      ,[CreationDateID]
--      ,[SetUpOrAmendTimeID]
--      ,[CreatedByOfficerID]
--      ,[TerminationOfficerID]
--      ,[TerminationDateID]
--      ,[TerminationSetupTimeID]
--      ,[Value]
--      ,[FixedChargeApplicableInd]
--      ,[BackdatedChargesProcessed]
--      ,[PolicyChargeValue])

--SELECT ISNULL(P.[PropertyID],0) AS [PropertyID],
--	ISNULL(TT.[TransactionTypeID],0) AS [TransactionTypeID],
--	ISNULL(EFF.DateID,0) AS [ChargeEffectiveFromDateID],
--	ISNULL(EFT.DateID,0) AS [ChargeEffectiveToDateID],
--	ISNULL(CD.DateID,0) AS [CreationDateID],
--	ISNULL(ST.[TimeID],0) AS [SetUpOrAmendTimeID],
--	ISNULL(CO.[OfficerID],0) AS [CreatedByOfficerID],
--	ISNULL(TEO.OfficerID,0) AS [TerminationOfficerID],
--	ISNULL(TD.DateID,0) AS [TerminationDateID],
--	ISNULL(TET.[TimeID],0) AS [TerminationSetupTimeID],
--	PC.[Value],
--	PC.[FixedChargeApplicableInd],
--	PC.[BackdatedChargesProcessed],
--	PC.[PolicyChargeValue]

--FROM [ims@Transform].[hmsTrn].[PropertyCharge] PC LEFT JOIN
--	 [hmsPrs].[DimProperty] P ON PC.PropertySequenceNumber = P.PropertySequenceNumber
--								AND P.[EffectiveFromDate] <= PC.[EffectiveFrom]
--								AND (P.[EffectiveToDate] >= PC.[EffectiveFrom] OR P.[EffectiveToDate] IS NULL) LEFT JOIN
--	 [hmsPrs].[DimTransactionType] TT ON PC.TransactionCode = TT.TransactionCode LEFT JOIN
--	 [hmsPrs].[DimOfficer] CO ON PC.[CreatedByCouncilOfficeCode] = CO.[OfficerCode] LEFT JOIN
--	 [hmsPrs].[DimOfficer] TEO ON PC.[TerminationOfficerCode] = TEO.[OfficerCode] LEFT JOIN
--	 [hmsPrs].[DimDate] EFF ON PC.[EffectiveFrom] = EFF.[Date] LEFT JOIN
--	 [hmsPrs].[DimDate] EFT ON PC.[EffectiveTo] = EFT.[Date] LEFT JOIN
--	 [hmsPrs].[DimDate] TD ON PC.[TerminationDate] = TD.[Date] LEFT JOIN
--	 [hmsPrs].[DimTime] ST ON PC.[SetUpOrAmendTime] = ST.SecsInDay LEFT JOIN
--	 [hmsPrs].[DimTime] TET ON PC.[TerminationSetupTime] = TET.SecsInDay LEFT JOIN
--	 [hmsPrs].[DimDate] CD ON PC.[CreationDate] = CD.[Date]

--CREATE VIEW vFactPropertyChargeWithTenancy AS
SELECT fpc.PropertyChargeID,
       fpc.PropertyID,
       dt.TenancyID,
       fpc.TransactionTypeID,
       fpc.ChargeEffectiveFromDateID,
       fpc.ChargeEffectiveToDateID,
       fpc.value,
	   dt.TenancyCommencementDate,
       dt.TenancyTerminationDate,
	   dt.TenancyNumber,
       fpc.CreationDateID,
       fpc.SetUpOrAmendTimeID,
       fpc.CreatedByOfficerID,
       fpc.TerminationOfficerID,
       fpc.TerminationDateID,
       fpc.TerminationSetupTimeID,
       fpc.Value,
       fpc.FixedChargeApplicableInd,
       fpc.BackdatedChargesProcessed,
       fpc.PolicyChargeValue
FROM IMS@Present.HmsPrs.FactPropertyCharge fpc
INNER JOIN IMS@Present.HmsPrs.DimDate effectiveFrom ON effectiveFrom.DateID = fpc.ChargeEffectiveFromDateID
INNER JOIN IMS@Present.HmsPrs.DimDate effectiveTo ON effectiveTo.DateID = fpc.ChargeEffectiveToDateID
    INNER JOIN IMS@Present.HmsPrs.DimProperty dp
        ON dp.PropertyID = fpc.PropertyID
    INNER JOIN IMS@Present.HmsPrs.DimTenancy dt
        ON dt.PropertySequenceNumber = dp.PropertySequenceNumber
           AND dt.[Current] = 1
           AND
           (
               (
                   dt.TenancyCommencementDate >= effectiveFrom.Date
                   AND (dt.TenancyCommencementDate <= effectiveTo.Date OR fpc.ChargeEffectiveToDateID = 0)
                   AND
                   (
                       dt.TenancyTerminationDate <= effectiveTo.Date OR fpc.ChargeEffectiveToDateID = 0
                       OR dt.TenancyTerminationDate IS NULL
                   )
               )
               OR
               (
                   dt.TenancyCommencementDate <= effectiveFrom.Date
                   AND
                   (
                       dt.TenancyTerminationDate >= effectiveFrom.Date OR (fpc.ChargeEffectiveToDateID = 0 AND dt.TenancyTerminationDate >= effectiveFrom.Date)
                       OR dt.TenancyTerminationDate IS NULL
                   )
               )
           )
		   --WHERE dt.TenancyNumber = 682104
		   --WHERE fpc.PropertyID IN (SELECT PropertyID FROM IMS@Present.hmsPrs.DimProperty WHERE PropertySequenceNumber = 812)
		   --AND dt.TenancyID IN (88735, 88695) ORDER BY TenancyID