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 summarize products transactions?

Execute the following SQL Server Transact-SQL scripts in Management Studio Query Editor to create a transaction summary report for (bicycle) frame products in 2004.

-- SQL common table expression - cte - SQL inner join group by - SQL inner join cte

-- SQL like wildcard search - SQL sum aggregate function - group by

USE AdventureWorks;

WITH cteTransactionHistory(ProductID,OrderQuantity)

     AS (SELECT   ProductID,

                  Sum(Quantity)

         FROM     Production.TransactionHistory

         WHERE    Year(TransactionDate) = 2004

         GROUP BY ProductID)

SELECT   [Products: Frame, 2004] = pp.Name,

         cte.ProductID,

         cte.OrderQuantity,

         ProductModel = pm.Name,

         SubCategory = ps.Name

FROM     Production.Product pp

         INNER JOIN cteTransactionHistory AS cte

           ON cte.ProductID = pp.ProductID

         INNER JOIN Production.ProductModel pm

           ON pm.ProductModelID = pp.ProductModelID

         INNER JOIN Production.ProductSubcategory ps

           ON pp.ProductSubCategoryID = ps.ProductSubCategoryID

WHERE    pp.Name LIKE ('%frame%')

         AND pp.ProductModelID IS NOT NULL

ORDER BY pp.Name

GO

/* Partial results

 

Products: Frame, 2004         ProductID   OrderQuantity     ProductModel

HL Road Frame - Black, 58     680         209               HL Road Frame

HL Road Frame - Red, 44       718         88                HL Road Frame

HL Road Frame - Red, 48       719         3                 HL Road Frame

HL Road Frame - Red, 58       706         213               HL Road Frame

*/

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