Example of using Common Table Expressions or CTE in a view

by ebarcza 11/28/2012 1:33:00 PM
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


Tags:

Refresh DNS cache

by ebarcza 11/28/2012 7:03:00 AM
ipconfig/flushdns

Tags: , ,

Powered by BlogEngine.NET 1.5.0.7
Theme by Mads Kristensen

About the author

Name of author Author name
Something about me and what I do.

E-mail me Send mail

Calendar

<<  September 2017  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

Recent comments

Tags

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2017

Sign in