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
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:
Post a Comment