Tags
Complex SQL Challenges, complex sql statement(puzzle), Complex TSQL Challenge, Find Next value in SQL, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, msbi skills, MSBISkills, msbiskills.com, Next Value puzzle in SQL, Queries for SQL Interview, SQLSERVER, T SQL Puzzles, TSQL Queries
T-SQL Query | [Finding Next Value Puzzle] – Write a query which will extract the next value from the currentQuota for each row.
Sample Input
| BusinessEntityID | SalesYear | CurrentQuota |
| 275 | 2005 | 367000 |
| 275 | 2005 | 556000 |
| 275 | 2006 | 502000 |
| 275 | 2006 | 550000 |
| 275 | 2006 | 1429000 |
| 275 | 2006 | 1324000 |
Expected Output
| BusinessEntityID | SalesYear | CurrentQuota | NextCurrentData |
| 275 | 2005 | 367000 | 556000 |
| 275 | 2005 | 556000 | 502000 |
| 275 | 2006 | 502000 | 550000 |
| 275 | 2006 | 550000 | 1429000 |
| 275 | 2006 | 1429000 | 1324000 |
| 275 | 2006 | 1324000 | 0 |
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.
--Create table CREATE TABLE lag ( BusinessEntityID INT ,SalesYear INT ,CurrentQuota DECIMAL(20,4) ) GO --Insert Data 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' --Check data SELECT BusinessEntityID,SalesYear,CurrentQuota FROM lag |
Update May 14 | Solutions
-- --------------------------------------- --Sol 1 | Pawan Kumar Khowal --------------------------------------- ;WITH CTE AS ( SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk FROM lag ) SELECT BusinessEntityID,SalesYear,CurrentQuota , ISNULL(( SELECT TOP 1 CurrentQuota FROM CTE c2 WHERE c1.rnk < c2.rnk ),0) NextCurrentData FROM CTE c1 --------------------------------------- --Sol 2 | Pawan Kumar Khowal --------------------------------------- ;WITH CTE AS ( SELECT BusinessEntityID,SalesYear,CurrentQuota,ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk FROM lag ) SELECT BusinessEntityID,SalesYear,CurrentQuota ,ISNULL(LEAD(CurrentQuota) OVER (ORDER BY rnk),0) NextCurrentData FROM CTE c1 -- |
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Improving my SQL BI Skills 
Tried this working perfectly.
;WITH CTE AS
(
select BusinessEntityId, Salesyear, CurrentQuota, ROW_NUMBER() over(order by BusinessEntityid) as MyId
from lag
)
–select * from cte
SELECT c.BusinessEntityID, c.SalesYear, c.CurrentQuota, ISNULL(d.CurrentQuota, 0)
FROM CTE c LEFT JOIN CTE d ON C.MyId + 1= (D.MyID);
LikeLike
SELECT BusinessEntityID,SalesYear,CurrentQuota,LEAD(CurrentQuota,1,0) OVER (ORDER BY SalesYear) AS Previous FROM Lag
LikeLike
with CTE(BusinessEntityID,SalesYear,CurrentQuota)AS
(
SELECT ‘275’, ‘2005’,’367000′
UNION ALL
SELECT ‘275’, ‘2005’,’556000′
UNION ALL
SELECT ‘275’, ‘2006’,’502000′
UNION ALL
SELECT ‘275’, ‘2006’,’550000′
UNION ALL
SELECT ‘275’, ‘2006’,’1429000′
Union All
SELECT ‘275’, ‘2006’,’1324000′
)
select BusinessEntityID,SalesYear,CurrentQuota, (select max(b.CurrentQuota) from CTE b where b.CurrentQuota < a.CurrentQuota) as lagCurrentData
from CTE a
LikeLike