You work as a database administrator at manufacturing company named ABC.com. ABC.com has
a SQL Server 2012 database named ProductionDB. The ProductionDB database has a table
named Sites that was created using the following Transact-SQL code:
CREATE TABLE Sites (
SiteID int NOT NULL PRIMARY KEY,
Location int NOT NULL,
Manager nvarchar(200) NOT NULL,
Week smallint NOT NULL,
ItemsProduced int NOT NULL )
You want to write the Transact-SQL query that returns the number of items produced at each
location for each week. In addition, you want the result set to include a column named
PrevItemsProduced that holds the number of items produced at each location for the week before.
What Transact SQL statement would accomplish this task?
A.
SELECT Location, Week, ItemsProduced,
LEAD(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
B.
SELECT Location, Week, ItemsProduced,
FIRST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
C.
SELECT Location, Week, ItemsProduced,
LAG(ItemsProduced, 1, 0) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
D.
SELECT Location, Week, ItemsProduced,
LAST_VALUE(ItemsProduced) OVER (PARTITION BY Location ORDER BY Week) AS
PrevItemsProduced
FROM Sites
E.
SELECT Location, Week, ItemsProduced,
CUME_DIST( ) OVER (PARTITION BY Location ORDER BY Week) AS PrevItemsProduced
FROM Sites
Explanation:
?