USE [360Performance]
GO

/****** Object:  View [dbo].[vwBuilding]    Script Date: 11/28/2012 17:03:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*Select * from vwBuilding*/
ALTER VIEW [dbo].[vwBuilding]
AS
--CTE 1
WITH    LatestApp
          AS (SELECT    AppID
                        ,BuildingID
                        ,dSubmitted
                        ,sFeedback
                        ,sBomaMemberName
                        ,sBomaMemberNumber
                        ,IsTobyWinner
                        ,IsEarthWinner
                        ,IsRenewal
                        ,IsWritableOverride
                        ,dtCreate
                        ,gCreatedBy
                        ,dtMod
                        ,gModBy
                        ,ROW_NUMBER() OVER (PARTITION BY BuildingID Order by dtCreate Desc) AS RowNum
              FROM      tblApp)


,    
--CTE 2
LatestAppDetail
          AS (SELECT    AppDetailID
                        ,AppID
                        ,OwnerCompanyID
                        ,MgmtCompanyID
                        ,MgrContactID
                        ,Poc1ID
                        ,Poc2ID
                        ,AppDetailTypeID
                        ,sShipAddr1
                        ,sShipAddr2
                        ,sShipCity
                        ,sShipState
                        ,sShipZip
                        ,sShipCountry
                        ,dtCreate
                        ,gCreatedBy
                        ,dtMod
                        ,gModBy
                        ,ROW_NUMBER() OVER (PARTITION BY AppID Order by dtCreate Desc) AS RowNum
              FROM      tblAppDetail)

SELECT     
    b.BuildingID, b.CategoryID, b.BuildingTypeID, b.sBuildingName, 
    b.sDisplayName, b.sAddr1, b.sAddr2, b.sCity, b.sState, b.sZip, 
    b.sCountry, b.iSize, b.sYearBuilt,
          (SELECT     sLookUpValue
            FROM          dbo.tblLookupValue AS l
            WHERE      (LookupValueID = b.BuildingTypeID)) AS BuildingTypeName,
          (SELECT     sLookUpValue
            FROM          dbo.tblLookupValue AS l
            WHERE      (LookupValueID = b.CategoryID)) AS CategoryName, 
    app.AppID, app.dSubmitted, app.sFeedback, app.sBomaMemberName, app.sBomaMemberNumber, 
    app.IsTobyWinner, app.IsEarthWinner, app.IsRenewal, app.IsWritableOverride, 
    AppDetail.AppDetailID, AppDetail.OwnerCompanyID, AppDetail.MgmtCompanyID, 
    AppDetail.MgrContactID, AppDetail.Poc1ID, AppDetail.Poc2ID, 
    AppDetail.AppDetailTypeID, AppDetail.sShipAddr1, AppDetail.sShipAddr2, AppDetail.sShipCity, 
    AppDetail.sShipState, AppDetail.sShipZip, AppDetail.sShipCountry, 
    poc2.sFullName AS Poc2sFullName, poc2.sPhone AS Poc2sPhone, poc2.sExt AS Poc2sExt, 
    poc2.sEmail AS Poc2sEmail, poc1.sFullName AS Poc1sFullName, poc1.sPhone AS Poc1sPhone, 
    poc1.sExt AS Poc1sExt, poc1.sEmail AS Poc1sEmail,
          (SELECT     sFullName
            FROM          dbo.tblContact AS c
            WHERE      (ContactID = AppDetail.MgrContactID)) AS ManagerName, app.dtCreate AS dtAppCreate, app.gCreatedBy AS gAppCreatedBy, 
    app.dtCreate AS dtAppModDate, app.gModBy AS gAppModBy, dbo.aspnet_Membership.UserId
FROM         
    dbo.aspnet_Membership RIGHT OUTER JOIN
    dbo.tblBuilding AS b ON dbo.aspnet_Membership.UserId = b.gCreatedBy LEFT OUTER JOIN
    --Reference CTE 1
    (Select * from LatestAppDetail where RowNum = 1)AS AppDetail LEFT OUTER JOIN
    dbo.tblContact AS poc1 ON AppDetail.Poc1ID = poc1.ContactID LEFT OUTER JOIN
    dbo.tblContact AS poc2 ON AppDetail.Poc2ID = poc2.ContactID RIGHT OUTER JOIN
    --Reference CTE 2
    (Select * from LatestApp where RowNum = 1) AS app 
    ON AppDetail.AppID = app.AppID ON b.BuildingID = app.BuildingID

GO