Question
You have a Fabric workspace that contains a warehouse named DW1. DW1 contains the following tables and columns.
| Table name | Column name | Description |
|---|---|---|
| SalesOrderDetail | ProductID | Contains the product ID of the ordered product |
| SalesOrderDetail | ModifiedDate | Contains the date of an order |
| SalesOrderDetail | OrderQty | Contains the order quantity |
| Product | ProductID | Contains the unique ID of a product |
| Product | Name | Contains a product name |
You need to create an output that presents the summarized values of all the order quantities by year and product. The results must include a summary of the order quantities at the year level for all the products.
How should you complete the code? To answer, select the appropriate options in the answer area.
```sql
[Dropdown 1] (SO.ModifiedDate) AS OrderDate
,P.Name AS ProductName
,SUM(SO.OrderQty) AS OrderQty
FROM [dbo].[SalesOrderDetail] SO
INNER JOIN [dbo].[Product] P
ON P.ProductID = SO.ProductID
GROUP BY
[Dropdown 2]
ORDER BY OrderDate
```
NOTE: Each correct selection is worth one point.
Explanation
Dropdown 1: SELECT YEAR returns the year part of ModifiedDate, which is the grouping level the output needs; CAST and CONVERT would need a target data type and would not extract the year from the date. Dropdown 2: ROLLUP(YEAR(SO.ModifiedDate), P.Name) produces the year-and-product rows plus a subtotal row for each year across all products (and a grand total), which is the hierarchical summary that is asked for. A plain GROUP BY YEAR(SO.ModifiedDate), P.Name returns no year-level subtotal, and CUBE also adds per-product totals across all years, which is not requested. The listed GROUPING SETS returns the same detail and yearly rows without the grand total, but ROLLUP is the construct intended for hierarchical year-then-product subtotals.
