Left Join on the same table basics

Im trying to create the following table:

JpId | JpName | JpValue | JpEndvalue
Where JpEndValue is JpValue (TimeStamp + 1day) from the same table but I get this result:

  • SQL Server - Filter field contents to numbers only
  • How to trim everything after certain character in sql
  • Creating a SQL table from a comma concatenated list
  • SQL Server Storage Plugin not Working
  • SQL Server : insert data into temporary table using a variable
  • Option Recompile makes query fast - good or bad?
  • Table:

    JpId JpName JpTimeStamp JpValue
    
    1   MAGIC   2017-06-15  151
    2   BASIC   2017-06-15  152
    3   MINI    2017-06-15  153
    4   SUPER   2017-06-15  154
    5   MAGIC   2017-06-16  161
    6   BASIC   2017-06-16  162
    7   MINI    2017-06-16  163
    8   SUPER   2017-06-16  164
    

    Query

    SELECT jp1.JpId
        ,jp1.JpName
        ,jp1.JpValue
        ,jp2.JpValue AS 'JpEndValue'
    FROM jackpot_web_report jp1
    LEFT JOIN jackpot_web_report jp2
        ON jp2.JpTimeStamp = '2017-06-16'
    WHERE jp1.JpTimeStamp = '2017-06-15';
    

    Result:

    1   MAGIC   151 161
    1   MAGIC   151 162
    1   MAGIC   151 163
    1   MAGIC   151 164
    2   BASIC   152 161
    2   BASIC   152 162
    2   BASIC   152 163
    2   BASIC   152 164
    3   MINI    153 161
    3   MINI    153 162
    3   MINI    153 163
    3   MINI    153 164
    4   SUPER   154 161
    4   SUPER   154 162
    4   SUPER   154 163
    4   SUPER   154 164
    

    Expected result:

    1   MAGIC   151 161
    2   BASIC   152 162
    3   MINI    153 163
    4   SUPER   154 164
    

    One Solution collect form web for “Left Join on the same table basics”

    I seems you missed jp2.JpName = jp1.JpName condition:

    SELECT jp1.JpId
        ,jp1.JpName
        ,jp1.JpValue
        ,jp2.JpValue AS 'JpEndValue'
    FROM jackpot_web_report jp1
    LEFT JOIN jackpot_web_report jp2
        ON jp2.JpTimeStamp = '2017-06-16' AND jp2.JpName = jp1.JpName
    WHERE jp1.JpTimeStamp = '2017-06-15';
    

    you can also add DATEADD in join condition:

    SELECT jp1.JpId
        ,jp1.JpName
        ,jp1.JpValue
        ,jp2.JpValue AS 'JpEndValue'
    FROM jackpot_web_report jp1
    LEFT JOIN jackpot_web_report jp2
        ON jp2.JpTimeStamp = DATEADD(DAY, 1, jp1.JpTimeStamp) AND jp2.JpName = jp1.JpName
    WHERE jp1.JpTimeStamp = '2017-06-15';
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.