RogerCooper
Registered User.
- Local time
- Today, 09:19
- Joined
- Jul 30, 2014
- Messages
- 387
I have a keyed external SQL server table called dbo_HISTORY that contains a summary of sales data. It has the fields:
INV_NUMBER, LINE_NO, MONTH, YEAR, CUSTOMER_ID, SALESMAN, SEGMENT, NET_SALES, KEY (and other fields not relevant to the question).
(Segment refers to individual markets that a customer might sell in)
(Key is equal to CUSTOMER_ID & SEGMENT and is created to make this query easier)
Our sales department would like a query that shows each customer_id/segment combination on single line that contains the following information:
Total Sales by Year, Year-to-Date Sales by Year, and % change in YTD. I want this to be a query that does not need to be changed each year. HISTORY only contains 5 calendar years (plus the current year)
This requires multiple queries of course.
Query#1: Customer Sales by Year-Segment-FY
Query#2: Customer Sales by Year-Segment-YTD
Query#3: Customer Sales by Year-YTD-Change%
These 3 queries are working correctly
Query#4
This also works correctly. The segment column is displayed as "SEGMENT".
Query#5
Now the segment column displays as "Customer Sales by Year-Segment-FY.SEGMENT". All I did was add another left join. The added query does not have have any columns named SEGMENT. This is largely a cosmetic issue but I wonder why it is displaying this way. Is there a better way to solve the underlying request.
INV_NUMBER, LINE_NO, MONTH, YEAR, CUSTOMER_ID, SALESMAN, SEGMENT, NET_SALES, KEY (and other fields not relevant to the question).
(Segment refers to individual markets that a customer might sell in)
(Key is equal to CUSTOMER_ID & SEGMENT and is created to make this query easier)
Our sales department would like a query that shows each customer_id/segment combination on single line that contains the following information:
Total Sales by Year, Year-to-Date Sales by Year, and % change in YTD. I want this to be a query that does not need to be changed each year. HISTORY only contains 5 calendar years (plus the current year)
This requires multiple queries of course.
Query#1: Customer Sales by Year-Segment-FY
Code:
TRANSFORM Sum(CCur([NET_SALES])) AS Expr2
SELECT dbo_HISTORY.Customer_ID, dbo_HISTORY.CUSTOMER_NAME, dbo_HISTORY.SALESMAN, dbo_HISTORY.SEGMENT, dbo_HISTORY.Customer_ID_Segment AS [Key]
FROM [Close Information], (Segment INNER JOIN dbo_HISTORY ON Segment.Segment = dbo_HISTORY.SEGMENT) INNER JOIN dbo_Spec_Part ON dbo_HISTORY.PRODUCT = dbo_Spec_Part.PART_ID
WHERE (((dbo_HISTORY.dATE)<=[Period End]) AND ((dbo_Spec_Part.Excluded)="N") AND ((Segment.Excluded)=False))
GROUP BY dbo_HISTORY.Customer_ID, dbo_HISTORY.CUSTOMER_NAME, dbo_HISTORY.SALESMAN, dbo_HISTORY.SEGMENT, dbo_HISTORY.Customer_ID_Segment
ORDER BY dbo_HISTORY.Customer_ID, dbo_HISTORY.Customer_ID_Segment
PIVOT [Year] & " FY";
Query#2: Customer Sales by Year-Segment-YTD
Code:
TRANSFORM Sum(CCur([NET_SALES])) AS Expr2
SELECT dbo_HISTORY.Customer_ID_Segment AS K
FROM [Close Information], (Segment INNER JOIN dbo_HISTORY ON Segment.Segment = dbo_HISTORY.SEGMENT) INNER JOIN dbo_Spec_Part ON dbo_HISTORY.PRODUCT = dbo_Spec_Part.PART_ID
WHERE (((dbo_HISTORY.dATE)<=[Period End]) AND ((dbo_Spec_Part.Excluded)="N") AND ((Segment.Excluded)=False) AND ((dbo_HISTORY.MONTH)<=[Month#]))
GROUP BY dbo_HISTORY.Customer_ID_Segment
ORDER BY dbo_HISTORY.Customer_ID_Segment
PIVOT [Year] & " YTD";
Query#3: Customer Sales by Year-YTD-Change%
Code:
SELECT [Customer Sales by Year-Segment-YTD-1].Customer_ID, [Customer Sales by Year-Segment-YTD-0].Sales AS [YTD-0], [Customer Sales by Year-Segment-YTD-1].Sales AS [YTD-1], CInt((Nz([Customer Sales by Year-Segment-YTD-0]![Sales])/[Customer Sales by Year-Segment-YTD-1]![Sales]-1)*100) AS [Change%], [Customer Sales by Year-Segment-YTD-1].Key
FROM [Customer Sales by Year-Segment-YTD-1] LEFT JOIN [Customer Sales by Year-Segment-YTD-0] ON [Customer Sales by Year-Segment-YTD-1].Key = [Customer Sales by Year-Segment-YTD-0].Key
WHERE ((([Customer Sales by Year-Segment-YTD-1].Sales)>0))
ORDER BY [Customer Sales by Year-Segment-YTD-1].Customer_ID, [Customer Sales by Year-Segment-YTD-1].Key;
These 3 queries are working correctly
Query#4
Code:
SELECT [Customer Sales by Year-Segment-FY].*, [Customer Sales by Year-Segment-YTD].*
FROM [Customer Sales by Year-Segment-FY] LEFT JOIN [Customer Sales by Year-Segment-YTD] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD].K;
This also works correctly. The segment column is displayed as "SEGMENT".
Query#5
Code:
SELECT [Customer Sales by Year-Segment-FY].*, [Customer Sales by Year-Segment-YTD].*, [Customer Sales by Year-Segment-YTD-Change%].[Change%] AS [YTD Change%], [Customer Sales by Year-Segment-% of LY].[% of LY]
FROM (([Customer Sales by Year-Segment-FY] LEFT JOIN [Customer Sales by Year-Segment-YTD] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD].K) LEFT JOIN [Customer Sales by Year-Segment-YTD-Change%] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-YTD-Change%].Key) LEFT JOIN [Customer Sales by Year-Segment-% of LY] ON [Customer Sales by Year-Segment-FY].Key = [Customer Sales by Year-Segment-% of LY].Key;
Now the segment column displays as "Customer Sales by Year-Segment-FY.SEGMENT". All I did was add another left join. The added query does not have have any columns named SEGMENT. This is largely a cosmetic issue but I wonder why it is displaying this way. Is there a better way to solve the underlying request.