Hello Experts.
Im Caught up on a query.
I need to retrieve sales order information pulling from two tables, along with the cost of unshipped items pulling from a 3rd table.
Example:
tblSalesOrder
SalesOrderID UserID SalesPersonID OrderStatusID
1 25 11503 1
2 12 11503 1
5 25 11503 2
3 12 11306 3
tblUserInfo
UserID FirstName LastName Company
12 Jane Smith Microsoft
25 Jon Doe Acme
11503 Sales Person MyCompany
11306 Other Sales MyCompany
tblSalesOrdrItems
SalesOrderID Quantity BOQuantity AppliedPrice
1 1 0 100
1 2 2 75
1 1 1 20
2 2 2 500
3 1 0 99
Rough Draft of Query
Select
'Sales Order #' + convert(varchar(4), a.SalesOrderID ) as Type , b.FirstName + ' ' + b.LastName as 'Name' , b.Company , a.DateCreated as 'Date' , sum(c.BOQuantity * c.AppliedPrice) as total
from
tblSalesOrder a, tblUserInfo b
inner join tblsalesorderitems c
on c.salesorderid = a.salesorderid
where a.userid = b.userid AND a.SalesPersonID = 11503 and a.datecreated >= '2006-07-01 00:00:00' AND a.datecreated <= '2006-07-28 23:59:59' And a.orderstatusid = 1 Order By 'Date' Desc
Current Error:
"The column prefix 'a' does not match with a table name or alias name used in the query."
Desired Relults:
Type Name Company Date Total
Sales Order#1 Jon Doe Acme #### 170
Sales Order#2 Jane Smith Microsoft #### 1000
I have tried a few different ways to write this with no success. the hard part for me is that the "sum" part of the select requires its own where clause.
I have also tried a "group by" with similar faliure.
I would prefer not to write this as a stored procedure as it is only used in 1 place in the program.
i also want to stay away from multiple queries, as that would be slow.
Please help.
Thx in advance
Anthony
Start Free Trial
