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

*/

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