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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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