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 construct purchase summary for all vendors?

Execute the following SQL Server T-SQL script in SSMS Query Editor to obtain the contact info and total purchase activity for each vendor of AdventureWorks mountain, road & touring bike company:

-- SQL inner join - SQL currency formatting - SQL group by

USE AdventureWorks;

SELECT   v.[Name]                                   AS Vendor,

         ct.[Name]                                  AS [ContactType],

         c.[FirstName] + ' ' + c.[LastName]         AS ContactName,

         c.[Phone],

         c.[EmailAddress],

         c.[EmailPromotion],

         [StateProvinceName] = sp.[Name],

         [CountryRegionName] = cr.[Name],

         '$' + convert(VARCHAR,sum(poh.TotalDue),1) AS TotalPurchaseAmount,

         count(poh.PurchaseOrderID)                 AS TotalPurchaseCount

FROM     [Purchasing].[PurchaseOrderHeader] poh

         INNER JOIN [Purchasing].[Vendor] v

           ON poh.VendorID = v.VendorID

         INNER JOIN [Purchasing].[VendorContact] vc

           ON vc.[VendorID] = v.[VendorID]

         INNER JOIN [Person].[Contact] c

           ON c.[ContactID] = vc.[ContactID]

         INNER JOIN [Person].[ContactType] ct

           ON vc.[ContactTypeID] = ct.[ContactTypeID]

         INNER JOIN [Purchasing].[VendorAddress] va

           ON va.[VendorID] = v.[VendorID]

         INNER JOIN [Person].[Address] a

           ON a.[AddressID] = va.[AddressID]

         INNER JOIN [Person].[StateProvince] sp

           ON sp.[StateProvinceID] = a.[StateProvinceID]

         INNER JOIN [Person].[CountryRegion] cr

           ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

GROUP BY v.[Name],

         ct.[Name],

         c.[FirstName] + ' ' + c.[LastName],

         c.[Phone],

         c.[EmailAddress],

         c.[EmailPromotion],

         sp.[Name],

         cr.[Name]

ORDER BY sum(poh.TotalDue) DESC;

GO

/* Partial results

 

Vendor ContactType ContactName Phone
Superior Bicycles Sales Associate Stu Osborn 472-555-0100
Professional Athletic Consultants Sales Associate Albert Rhodes 504-555-0100
Chicago City Saddles Sales Manager Stacy Rizzi 329-555-0100
Jackson Authority Sales Agent Adam Reynolds 574-555-0100
Vision Cycles, Inc. Sales Associate Scott Oveson 939-555-0100
Sport Fan Co. Sales Associate Ethel Porter 665-555-0100
Proseware, Inc. Sales Manager Eric Rhodes 113-555-0100

*/

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

 

 

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