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 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
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