Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
T-SQL Query | [ The Shortest Distance Puzzle ] / [ Graph Shortest Path Puzzle ]
Puzzle Statement
- Input table “Area” contains Distance FromPoint , ToPoint and the distance between these points or Areas.
- The Puzzle is that you have find out the shortest distance between FromPoint to ToPoint considering all the Paths
- Please check out the sample input and expected output for details.
Sample Input
| ID | FromPoint | ToPoint | Distance |
| 0 | NULL | SECTOR 5 | 0 |
| 1 | SECTOR 5 | SECTOR 22 | 5 |
| 2 | SECTOR 5 | SECTOR 23 | 10 |
| 3 | SECTOR 22 | SECTOR 23 | 15 |
| 4 | SECTOR 23 | SECTOR 14 | 20 |
| 5 | SECTOR 22 | SECTOR 14 | 25 |
| 6 | SECTOR 5 | SECTOR 14 | 35 |
| 6 | SECTOR 22 | SECTOR 31 | 40 |
| 6 | SECTOR 23 | SECTOR 31 | 45 |
Expected Output
| Paths | Distance |
| .SECTOR 5..SECTOR 23..SECTOR 14. | 30 |
| .SECTOR 5..SECTOR 22..SECTOR 14. | 30 |
Rules/Restrictions
- Your solution should be should use “SELECT” statement or “CTE”
- Your solution should be generic in nature.
- Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com
Script
Use the below script to generate the source table and fill them up with the sample data.
-- CREATE TABLE Area ( ID INT ,FromPoint VARCHAR(10) ,ToPoint VARCHAR(10) ,Distance INT ) GO INSERT INTO Area VALUES (0 ,NULL , 'SECTOR 5' ,0), (1 ,'SECTOR 5' , 'SECTOR 22' ,05), (2 ,'SECTOR 5' , 'SECTOR 23' ,10), (3 ,'SECTOR 22', 'SECTOR 23' ,15), (4 ,'SECTOR 23', 'SECTOR 14' ,20), (5 ,'SECTOR 22', 'SECTOR 14' ,25), (6 ,'SECTOR 5' , 'SECTOR 14' ,35), (6 ,'SECTOR 22', 'SECTOR 31' ,40), (6 ,'SECTOR 23', 'SECTOR 31' ,45) -- |
Update Apr 29 | Solution 1
--
DECLARE @StartingPoint AS VARCHAR(50) = 'SECTOR 5'
DECLARE @EndingPoint AS VARCHAR(50) = 'SECTOR 14'
;WITH CTE1 AS
(
SELECT ToPoint ,
CASE WHEN FromPoint IS NULL THEN CAST('.'+ISNULL(FromPoint,ToPoint)+'.' AS VARCHAR(MAX))
WHEN FromPoint IS NOT NULL THEN CAST('.'+FromPoint+'.'+ToPoint+'.' AS VARCHAR(MAX))
END FullyQualifiedName
, Distance FinalDistance
FROM Area WHERE ( FromPoint = @StartingPoint )
UNION ALL
SELECT a.ToPoint , c.FullyQualifiedName+'.'+a.ToPoint+'.' FullyQualifiedName
, FinalDistance + a.Distance FinalDistance
FROM Area a INNER JOIN CTE1 c ON a.FromPoint = c.ToPoint
)
,CTE2 AS
(
SELECT * , RANK() OVER (ORDER BY FinalDistance) rnk FROM CTE1
WHERE ToPoint = @EndingPoint AND PATINDEX('%'+@EndingPoint+'%',FullyQualifiedName) > 0
)
SELECT FullyQualifiedName, FinalDistance FROM CTE2 WHERE rnk = 1
--
|
Add a comment if you have any other solution in mind. We all need to learn.
Keep Learning
Http://MSBISkills.com
Improving my SQL BI Skills 
DECLARE @FromPoint VARCHAR(100)=’SECTOR 5′
DECLARE @ToPoint VARCHAR(100)=’SECTOR 14′
SELECT TOP 1 WITH TIES FullPath,TotalDistance FROM
(
SELECT
CASE WHEN tbl3.FROMPoint=@FromPoint
THEN
CASE WHEN tbl3.ToPoint=tbl2.FROMPoint
THEN tbl3.FROMPoint+’..’+tbl3.ToPoint+’..’+tbl2.ToPoint
ELSE ”
END
ELSE
CASE WHEN tbl2.FROMPoint=@FromPoint
THEN tbl2.FROMPoint+’.’+tbl2.ToPoint
ELSE ”
END
END AS FullPath,
CASE WHEN tbl3.FROMPoint=@FromPoint
THEN
CASE WHEN tbl3.ToPoint=tbl2.FROMPoint
THEN tbl3.Distance+tbl2.Distance
ELSE ”
END
ELSE
CASE WHEN tbl2.FROMPoint=@FromPoint
THEN tbl2.Distance
ELSE ”
END
END AS TotalDistance
FROM
(
SELECT ID,FROMPoint,ToPoint,Distance,rn
FROM
(
SELECT ID,FROMPoint,ToPoint,Distance,ROW_NUMBER() OVER(ORDER BY %%Physloc%%) AS rn
FROM Area
)tbl1
WHERE ToPoint=@ToPoint
)tbl2
inner join
(
SELECT ID,FROMPoint,ToPoint,Distance,ROW_NUMBER() OVER(ORDER BY %%Physloc%%) AS rn
FROM Area
)tbl3 on tbl2.FROMPoint=tbl3.ToPoint
)tbl4
WHERE TotalDistance!=0
ORDER BY TotalDistance
LikeLike
Hi,
Thank you for the solution.
Regards
LikeLike