Which Transact-SQL query should you use?

Posted by: Pdfprep Category: 70-461 Tags: , ,

A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.

You need to create a report that displays the profits made by each territory for each year and its previous year.

Which Transact-SQL query should you use?
A . SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit
FROM Profits
B . SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit
FROM Profits
C . SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit
FROM Profits

D . SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit
FROM Profits

Answer: C

Explanation:

Reference: http://msdn.microsoft.com/en-us/library/hh231256.aspx

Reference: http://msdn.microsoft.com/en-us/library/hh213125.aspx

Leave a Reply

Your email address will not be published.