leading-project/dw/dim/dim_project_base_info_d.sql

-- *****************************
-- 修改人： 杨坤安
-- 修改日期: 2024-09-06
-- 修改内容： 一个项目接管面积会有多个，先累加再关联
-- 修改日期: 2024-09-24
-- 修改内容: 项目对应业态取值规则变更
-- 修改日期: 2024-10-09
-- 修改内容: 新增合同到期时间
-- *****************************
select
    b.CommId
     , case when b.CommKind = '0001' then '商住'
            when b.CommKind = '0002' then '商办'
            when b.CommKind = '0003' then '公建'
            when b.CommKind = '0004' then '住宅'
            when b.CommKind = '0005' then '商业'
            when b.CommKind = '0006' then '酒店'
    end StateName
     , b.ManageKind
     , case when b.ManageKind = '0001' then '项目内盘'
            when b.ManageKind = '0002' then '项目外盘（全委）'
            when b.ManageKind = '0003' then '项目外盘（合资）'
            when b.ManageKind = '0004' then '案场内盘'
            when b.ManageKind = '0005' then '案场外盘（全委）'
            when b.ManageKind = '0006' then '案场外盘（合资）'
    end ManageKindName
     , b.Province
     , b.City
     , b.Borough County
     , a.GetMethod
     , a.GetTime
     , a.ContractName
     , a.FloorArea
     , a.UndergroundArea
     , a.GreenArea
     , a.TakeOverArea
     , a.ContractArea
     , a.ManageContractArea
     , a.ContractTerm
     , a.ContractPartyName
     , a.PropertyMgrEntrustType
     , a.IsContractFiled
     , a.ProjectDeliverySituation
     , a.UnsoldParkingCount
     , a.PedEntrExitsCount
     , a.CarEntrExitsCount
     , a.MixedEntrExitsCount
     , a.TotalEntrExitsCount
     , a.GuardedEntrExitsCount
     , a.MonitoringRoomCount
     , a.IsMonRoomMergedToGate
     , a.commAddress
     , a.CommFrom
     , a.DevSubject
     , a.TakeOverTime
     , a.ManageStage
     , a.ChargeMode
     , a.IsSetCommittee
     , a.CommitteeTime
     , a.ProjectFloorPlan
     , a.CityLevel
     , a.CommitteeTermStartDate
     , a.CommitteeTermEndDate
     , b.DictionaryName AS StateNameNew
     , a.ContractEndTime  AS ContractEndTime
from (
         select
             c.CommId
              , right(StateName,4) StateName
              , a.PropertyMgrEntrustType
              , a.IsContractFiled
              , a.ContractPartyName
              , c.ProjectDeliverySituation
              , c.UnsoldParkingCount
              , c.PedEntrExitsCount
              , c.CarEntrExitsCount
              , c.MixedEntrExitsCount
              , c.TotalEntrExitsCount
              , c.GuardedEntrExitsCount
              , c.MonitoringRoomCount
              , c.IsMonRoomMergedToGate
              , a.GetMethod
              , a.GetTime
              , a.ContractName
              , a.FloorArea
              , a.UndergroundArea
              , a.GreenArea
              , a.ContractTerm
              , c.commAddress
              , c.CommFrom
              , c.DevSubject
              , c.TakeOverTime
              , c.ManageStage
              , c.ChargeMode
              , c.IsSetCommittee
              , c.CommitteeTime
              , c.ProjectFloorPlan
              , c.CityLevel
              , c.CommitteeTermStartDate
              , c.CommitteeTermEndDate
              , a.ContractEndTime -- 合同结束时间
              , sum(TakeOverArea) TakeOverArea
              , sum(ContractArea) ContractArea
              , sum(ManageContractArea) ManageContractArea
         from
             (
                 select
                     *
                 from ods_erp_tb_hspr_projecttakeoverinformation_d
                 where IsDelete = 0
             ) a
                 left join
             (
                 select
                     ParentId
                      ,SUM(TakeOverArea) AS TakeOverArea
                 from ods_erp_tb_hspr_projecttakeoverdetail_d
                 WHERE IsDelete = 0
                 group by
                     ParentId
             ) b
             on a.id = b.ParentId
                 right join (select * from ods_erp_tb_hspr_projectbasicinfomation_d where IsDelete = 0) c
                            on a.ParentId = c.id
         group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34
     ) a
         right join
     (
         SELECT
             b1.*
              ,b2.DictionaryName
         FROM
             (
                 select
                     *
                 from ods_erp_tb_hspr_community_d
                 where IsDelete = 0
             )b1
                 LEFT JOIN
             (-- add 2024-09-24  项目业态新规则取数
                 select
                     DictionaryCode -- 项目业态编码
                      ,DictionaryName -- 项目业态名称
                 from ods_erp_tb_dictionary_projectformat_d
             )b2
             ON B1.CommKind = b2.DictionaryCode

     ) b
     on a.CommId = b.CommId