T-SQL Query | [Finding Previous Value Puzzle] – Write a query which will extract the previous value from the currentQuota for each row.

Input

Output ..> We need to extract the last value from the currentQuota ( Last Column )

Rules/Restrictions
◾The solution should be should use “SELECT” statement or “CTE”.
◾Send your solution to pawankkmr@gmail.com
◾Do not post you solution in comments section

Script Use the below script to generate the source table and fill them up with the sample data.

--So we first create the table

CREATE TABLE lag
(
BusinessEntityID INT
,SalesYear   INT
,CurrentQuota  DECIMAL(20,4)
)
GO

INSERT INTO lag
SELECT 275 , 2005 , '367000.00'
UNION ALL
SELECT 275 , 2005 , '556000.00'
UNION ALL
SELECT 275 , 2006 , '502000.00'
UNION ALL
SELECT 275 , 2006 , '550000.00'
UNION ALL
SELECT 275 , 2006 , '1429000.00'
UNION ALL
SELECT 275 , 2006 ,  '1324000.00'

Update May 14 | Solution

--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/

;WITH CTE AS
( 
	SELECT  BusinessEntityID ,SalesYear ,CurrentQuota
	, ROW_NUMBER() OVER (ORDER BY BusinessEntityID ) AS ID
	FROM lag
)
SELECT c.BusinessEntityID ,c.SalesYear , c.CurrentQuota
	, ISNULL((SELECT TOP 1 d.CurrentQuota FROM CTE d WHERE c.ID > d.ID ORDER BY ID DESC ),0) lagCurrentData
FROM CTE c


/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

;WITH CTE AS
(
 SELECT BusinessEntityID ,SalesYear ,CurrentQuota ,ROW_NUMBER()OVER (ORDERBY BusinessEntityID )AS ID
 FROM lag
)
SELECT c.Id ,c.BusinessEntityID ,c.SalesYear , c.CurrentQuota,ISNULL(d.CurrentQuota,0) lagCurrentData
FROM CTE c LEFTOUTERJOIN CTE d ON c.ID =(d.ID+1)


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com