SIMULATION
You create a table named Products.Sales by running the following Transact-SQL statement:
CREATE TABLE Products.Sales (
SalesId int IDENTIFY(1,1) PRIMARY KEY,
SalesDate DateTime NOT NULL,
SalesAmount decimal(18,2) NULL
)
You add the following data to the table.
You are developing a report to display monthly sales data.
You need to create a Transact-SQL query to meet the following requirements:
Retrieve a column for the year followed by a column for each month from January through December.
Include the total sales amount for each month.
Aggregate columns by year, month, and then amount.
Construct the query using the following guidelines:
Use the MONTH keyword as the interval when using the DATANAME function.
Do not modify the provided IN clause.
Do not surround object names with square brackets.
Do not use implicit joins.
Do not use the DATEPART function.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer
area that resolves the problem and meets the stated goals or requirements. You can add code within the code
that has been provided as well as below it.
1. SELECT * FROM
2. (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month,
SalesAmount AS Amount
3.
4. ) AS MonthlySalesData
5.6. FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
Answer: See the explanation
Explanation:
1 SELECT * FROM
2 (SELECT YEAR(SalesData)) AS Year, DATENAME (MONTH, SalesDate) AS Month, SUM
(SalesAmount) AS Amount
3 FROM Products.Sales GROUP BY Year, Month
4 ) AS MonthlySalesData
5 PIVOT SUM(Amount)
6 FOR Month IN (January, February, March, April, May, June, July, August,
September, October, November, December))
AS MonthNamePivot
Note:
Line 2: Add SUM( ) around SalesAmount
Line 3: Add: FROM Products.Sales GROUP BY Year, Month
Line 5: Add: PIVOT SUM(Amount)