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

<<  November 2017  >>
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

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