Home 2017 August SQL Puzzle: SQL Advance Query – Find the shortest path between two Roads

SQL Puzzle: SQL Advance Query – Find the shortest path between two Roads

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to find the shortest path between the given two roads. (For example, find shortest path between road NH220 to NH320)

Input Data:

Expected Output: For shortest path between road NH220 to NH320

Create a table with data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: Use sp_clean_db_free_space to remove Ghost Records or Dead TuplesSQL Server: Use sp_recompile to recompile the stored procedures, triggers, and user-defined functions
Comments: 4
  1. NAVEEN
    October 12, 2017 at 10:10 am

    select TOP 1 A.DISTANCE+ISNULL(B.DISTANCE,0)SUM1,CONCAT(A.FromRoadNo,’..’,A.TOROADNO,’..’,ISNULL(B.TOROADNO,”)) from roads a
    left join roads b
    on a.toRoadNo=b.FromRoadNo
    where a.fromroadno=’NH220′ AND ( A.toroadno=’NH320′ OR B.TOROADNO=’NH320′)
    ORDER BY 1

    ReplyCancel
    • Anvesh Patel
      October 12, 2017 at 10:57 am

      thanks for sharing alternative, but try to do without any hard-code value like NH220.

      ReplyCancel
  2. Dinesh.IS
    October 12, 2017 at 3:03 pm

    DECLARE @StartingRoadNo AS VARCHAR(50) = ‘NH108’
    DECLARE @EndingRoadNo AS VARCHAR(50) = ‘NH120′

    ;
    WITH CTE
    As
    (Select Row_Number()Over(Order By ToRoadNo) As SLNo,Row_Number()Over(Partition By ToRoadNo Order By ToRoadNo Desc) PSLNo,FromRoadNo,ToRoadNo,Distance From Roads Where ((FromRoadNo=@StartingRoadNo) Or (ToRoadNo Between @EndingRoadNo And @EndingRoadNo))),
    N
    As
    (Select PSLNo,SLNo,FromRoadNo,ToRoadNo,Distance From CTE),
    F
    As
    (Select PSLNo,Sum(IsNull(Distance,0)) As Distance From N
    Group By PSLNo)
    Select @StartingRoadNo+’,’+(Select Convert(VarChar(4000),ToRoadNo)+’,’ From N Where N.PSLNo=F.PSLNo For XML PATH(”)) As Traval,Distance
    From F Where F.Distance=(Select Min(Distance) From F)

    ReplyCancel
  3. Dinesh.IS
    October 12, 2017 at 3:08 pm

    —Method-2
    DECLARE @StartingRoadNo AS VARCHAR(50) = ‘NH220’
    DECLARE @EndingRoadNo AS VARCHAR(50) = ‘NH320′

    ;
    WITH CTE
    As
    (Select Row_Number()Over(Order By ToRoadNo) As SLNo,Row_Number()Over(Partition By ToRoadNo Order By ToRoadNo Desc) PSLNo,
    FromRoadNo,ToRoadNo,Distance From Roads Where ((FromRoadNo=@StartingRoadNo) Or (ToRoadNo Between @EndingRoadNo And @EndingRoadNo))),
    N
    As
    (Select PSLNo,Sum(IsNull(Distance,0)) Distance From CTE
    Group By PSLNo)

    Select @StartingRoadNo+(Select Convert(VarChar(500),ToRoadNo)+’,’ From CTE B Where B.PSLNo=N.PSLNo For XML PATH(”)) As Traval,Distance
    From N Where Distance=(Select Min(Distance) From N)

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

August 26, 2017 4 Comments SQL Puzzle, , , , , , , , , ,