AdventureWorks2012:Identify SalesPerson

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

AdventureWorks2012:Identify SalesPerson

Author

Topic

gpspreet
Starting Member

8 Posts

Posted - 2013-03-30 : 05:28:57

Assignment:
Identify the sales persons who have sales less than $100,000 since beginning of 2008.
Show: Sales person login ID
Order by: Sales person login ID

WorkAround:
SELECT sp.BusinessEntityID,TotalSales = SUM(soh.TotalDue) -- SUM of all sales total amounts FROM Sales.SalesOrderHeader soh NNER JOIN Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID
WHERE soh.OrderDate >= '20080101' -- on all sales since Jan 1, 2008
GROUP BY sp.BusinessEntityID

Issue:- Not able to get LoginID from HR.Employee Table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-30 : 13:08:38

where's HR.Employee? you havent even used table in query then how will you get column from it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gpspreet
Starting Member

8 Posts

Posted - 2013-03-31 : 23:59:21

I found the Answer....

SELECT e.LoginID , spp.BusinessEntityID,spp.TotalSales FROM
(
SELECT sp.BusinessEntityID, TotalSales = SUM(soh.TotalDue) -- SUM of all sales total amounts
FROM Sales.SalesOrderHeader soh
INNER JOIN
Sales.SalesPerson sp ON soh.SalesPersonID = sp.BusinessEntityID
WHERE
soh.OrderDate >= '20080101' -- on all sales since Jan 1, 2008
GROUP BY
sp.BusinessEntityID
) spp

INNER JOIN
Person.Person p ON spp.BusinessEntityID = p.BusinessEntityID
INNER JOIN
HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID
WHERE
TotalSales < 100000.0 order by e.LoginID.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-01 : 00:27:05

great.. cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources