DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct dimensional hierarchy query?

Execute the following Microsoft SQL Server 2008 T-SQL AdventureWorks2008 database scripts in Query Editor to demonstrate the preparation of query with date and geography dimensional hierarchies. The dataset can be used in Reporting Services drill-down matrix report.

SELECT  

   st.[Group]                           AS Territory,

         st.CountryRegionCode                 AS Country,

         st.Name                              AS Region,

         Year(soh.OrderDate)                  AS OrderYear,

         'Q' + convert(CHAR(1),(

           Month(soh.OrderDate) - 1) / 3 + 1) AS OrderQuarter,

         datename(MONTH,soh.OrderDate)        AS OrderMonth,

         soh.TotalDue

FROM     Sales.SalesOrderHeader soh

         INNER JOIN Sales.SalesTerritory st

           ON st.TerritoryID = soh.TerritoryID

 

/*

Territory         Cnty  Region      YYYY  QQ    MM    TotalDue

North America     US    Southeast   2001  Q3    July  27231.5495

North America     US    Southeast   2001  Q3    July  1716.1794

North America     CA    Canada      2001  Q3    July  43561.4424

North America     CA    Canada      2001  Q3    July  38331.9613

North America     US    Southwest   2001  Q3    July  556.2026

*/

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE