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