Thursday, September 5, 2019

How to use LEAD) and LAG() functions

DECLARE @sales AS TABLE (BusinessEntityID INT, QuotaDate Date,   SalesQuota DECIMAL(9,2))

INSERT INTO @sales (BusinessEntityID, QuotaDate,   SalesQuota) 
VALUES
 (275 , '01-Jan-2005' ,  367000.00  )
,(285 , '01-Jan-2005' ,  556000.00  )
,(275 , '01-Jan-2007' ,  502000.00  )
,(285 , '01-Jan-2006' ,  550000.00  )
,(275 , '01-Jan-2006' ,  1429000.00 )
,(275 , '01-Jan-2010' ,  1324000.00 )
,(285 , '01-Jan-2005' ,  756000.00  )
,(275 , '01-Jan-2007' ,  402000.00  )
,(275 , '01-Jan-2008' ,  950000.00  )
,(275 , '01-Jan-2006' ,  1829000.00 )
,(275 , '01-Jan-2006' ,  114000.00 )

SELECT * FROM @sales --WHERE BusinessEntityID = 275
ORDER BY YEAR(QuotaDate)

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota
    , LEAD(SalesQuota, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS NextQuota 
FROM @sales
--WHERE BusinessEntityID = 275

SELECT BusinessEntityID, YEAR(QuotaDate) AS SalesYear, SalesQuota AS CurrentQuota
    , LAG(SalesQuota, 1,0) OVER (PARTITION BY BusinessEntityID ORDER BY YEAR(QuotaDate)) AS NextQuota 
FROM @sales
--WHERE BusinessEntityID = 275

No comments: