datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to create a view with a CTE?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to create views with sequence number and the SalesBySalesPerson CTE.

-- View returning sequential integers - Sequence number view

CREATE VIEW vSequence AS

WITH cteNumber AS

 (SELECT Nbr=ROW_NUMBER() OVER(ORDER BY (SELECT 1))

  FROM master.dbo.spt_values a CROSS JOIN master.dbo.spt_values b)

SELECT SeqNo=Nbr FROM cteNumber

GO

 

SELECT TOP(10000) SeqNo from vSequence ORDER BY SeqNo

------------

 

-- SQL create view with CTE (Common Table Expression)

USE AdventureWorks

GO

CREATE VIEW vSalesStaffQuickStats

AS

  WITH SalesBySalesPerson (SalesPersonID, NumberOfOrders, MostRecentOrderDate)

      AS

      (

            SELECT SalesPersonID, COUNT(*), MAX(OrderDate)

            FROM Sales.SalesOrderHeader

            GROUP BY SalesPersonID

      )

  SELECT E.EmployeeID,

         EmployeeOrders = OS.NumberOfOrders,

         EmployeeLastOrderDate = OS.MostRecentOrderDate,

         E.ManagerID,

         ManagerOrders = OM.NumberOfOrders,

         ManagerLastOrderDate = OM.MostRecentOrderDate

  FROM   HumanResources.Employee AS E

       INNER JOIN SalesBySalesPerson AS OS

         ON E.EmployeeID = OS.SalesPersonID

       LEFT OUTER JOIN SalesBySalesPerson AS OM

         ON E.ManagerID = OM.SalesPersonID

GO

 

-- T-SQL test view

SELECT * FROM vSalesStaffQuickStats

ORDER BY EmployeeID

GO

 

/* Partial results

 

EmployeeID  EmployeeOrders    EmployeeLastOrderDate

268         48                2004-06-01 00:00:00.000

275         450               2004-06-01 00:00:00.000

276         418               2004-06-01 00:00:00.000

277         473               2004-06-01 00:00:00.000

*/

 

 

Related articles:

 

Using Common Table Expressions

 

WITH common_table_expression (Transact-SQL)

 

CREATE VIEW (Transact-SQL)

 

 

Exam Prep 70-461
Exam 70-461