Here is what I am trying to acheieve:
One dataset with Sales Rep Name, Sales For each Month for said Rep, Target for each Month for that Rep, then two columns one that contains the month name and one the year for that particular row. These last two coulms have been made using DATEPART and DATENAME of a field in a view called Customers Required Date AS 'SOMonth' and 'SOYear'
The Dataset will eventually be limited by date range but for now returns all records for the 2 years we have been using the DB.
I have three views, vSalesOrderCall Offs - Shows all items that have been ordered by customers with value, item no, qty, customer code, etc.
The next is a vCustomerAddres ses, this stores customer addresses. I am using this view as it also has the Sales Rep Code for that address stored in it. So vSalesOrderCall Offs is joined with vCustomerAddres ses on two fields, Account Code and Address Code and it returns the Rep_Code of the order in vSalesOrderCall Offs.
Up to this point I am able to get all the Sales for Each Month for Each Rep pulled through, in one neat row.
Now when I add in the view that contains the targets (vRepsTargetsLi st) which is a simple view that has one row for each target for each month of each year.
I can join on rep code in the targets table but I am unable to join on SOMonth or SOYear. I get the error "unable to join on multipart identifier"
I hope I have made it clear what I am trying to do.
Here is my query.
One dataset with Sales Rep Name, Sales For each Month for said Rep, Target for each Month for that Rep, then two columns one that contains the month name and one the year for that particular row. These last two coulms have been made using DATEPART and DATENAME of a field in a view called Customers Required Date AS 'SOMonth' and 'SOYear'
The Dataset will eventually be limited by date range but for now returns all records for the 2 years we have been using the DB.
I have three views, vSalesOrderCall Offs - Shows all items that have been ordered by customers with value, item no, qty, customer code, etc.
The next is a vCustomerAddres ses, this stores customer addresses. I am using this view as it also has the Sales Rep Code for that address stored in it. So vSalesOrderCall Offs is joined with vCustomerAddres ses on two fields, Account Code and Address Code and it returns the Rep_Code of the order in vSalesOrderCall Offs.
Up to this point I am able to get all the Sales for Each Month for Each Rep pulled through, in one neat row.
Now when I add in the view that contains the targets (vRepsTargetsLi st) which is a simple view that has one row for each target for each month of each year.
I can join on rep code in the targets table but I am unable to join on SOMonth or SOYear. I get the error "unable to join on multipart identifier"
I hope I have made it clear what I am trying to do.
Here is my query.
Code:
SELECT SUM(vSalesOrderCallOffs.Price * vSalesOrderCallOffs.[Qty Ordered]) AS [Sales Total], vCustomerAddresses.[Rep Code], DATENAME(mm,
vSalesOrderCallOffs.[Customers Required Date]) AS SOMonth, DATEPART(yyyy, vSalesOrderCallOffs.[Customers Required Date]) AS SOYear,
vRepsTargetsList.Target
FROM vCustomerAddresses LEFT OUTER JOIN
vRepsTargetsList ON vCustomerAddresses.[Rep Code] = vRepsTargetsList.Rep_Code AND vRepsTargetsList.Mnth = 'February' AND
vRepsTargetsList.Year = '2012' RIGHT OUTER JOIN
vSalesOrderCallOffs ON vCustomerAddresses.[Address Code] = vSalesOrderCallOffs.[Order Address Code] AND
vCustomerAddresses.[Account Code] = vSalesOrderCallOffs.[Account Code]
GROUP BY vCustomerAddresses.[Rep Code], DATEPART(yyyy, vSalesOrderCallOffs.[Customers Required Date]), DATENAME(mm,
vSalesOrderCallOffs.[Customers Required Date]), vRepsTargetsList.Target
ORDER BY vCustomerAddresses.[Rep Code], SOYear, SOMonth