Which Transact-SQL query should you use?

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 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

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



Leave a Reply 4

Your email address will not be published. Required fields are marked *


Ghandi

Ghandi

C is correct the LAG function returns previous rows without the need for self joins, LEAD returns future rows (again without the need for self join). The question asks for previous year profits.

Durga Prasad Palepu

Durga Prasad Palepu

C