SQL Best way to JOIN day over day data into same row including new and deleted IDs

I initially thought this would be fairly simple but for some reason I’m struggling with it.

If I have a table that looks like this:

  • Compare phone numbers without considering any format in Linq to entities and check performance as well
  • Random select is not always returning a single row
  • transport-level error has occurred when receiving results from the server. error: 18 - Connection has been closed by peer
  • Local vs Global temp tables - When to use what?
  • Copy SQL Server database in code
  • How to Clear down Query Execution Statistics in SQL Server 2005/2008
  • table1
    Date        ID       Quantity
    2/21        1        100
    2/21        2        500
    2/21        3        200
    2/20        2        600
    2/20        3        400
    2/20        5        2000

    And I want to join this data to look like:

    ID        prev_date     prev_quantity    curr_date     curr_quantity
    1         2/20          0                2/21          100
    2         2/20          600              2/21          500
    3         2/20          400              2/21          200
    5         2/20          2000             2/21          0

    The tricky part being ID 1 doesn’t have an entry for previous day (so make 0) and ID 5 doesnt have an entry for current day (make that 0 as well)

    What is the best way to do this? Thanks in advance!!

    3 Solutions collect form web for “SQL Best way to JOIN day over day data into same row including new and deleted IDs”

    This may help u,,,,,

        ISNULL(T.DATE,GETDATE()) AS 'CurrDate',
        ISNULL(T.Quantity,0) AS 'CurrQty',
        ISNULL(T1.DATE,GETDATE()-1) AS 'PrevDate' ,
        ISNULL(T1.Quantity,0) AS 'PrevQty'
            FROM @TABLE T   
                MAX(T1.DATE) AS [DATE] 
          FROM @TABLE T1 
          GROUP BY  T1.ID,T1.Quantity
        ) T1 ON T.ID = T1.ID 
    SELECT  curr.ID, ISNULL(Prev.date, DATEADD(DD, -1, curr.Date)) AS Prev_Date,        
            ISNULL(prev.Quantity, 0) AS Prev_Quantity, 
            ISNULL(curr.date, DATEADD(DD, 1, prev.Date)) AS Curr_Date, ISNULL(curr.Quantity, 0) AS Curr_Quantity
    FROM    table1 curr
                FULL OUTER JOIN
            table1 prev
            ON curr.ID = prev.ID AND prev.Date = DATEADD(DD, -1, curr.Date) 

    You could solve the gaps problem using a rank. Also instead of ISNULL use a UNION:

    SELECT RANK() OVER (ORDER BY ID,DATE) rank, ID, date, quantity
    INTO temp1
    FROM (
    SELECT date, ID, quantity
              FROM table1
            UNION ALL
            SELECT dateadd(day,-1,MIN(date)) date, ID, 0 quantity
              FROM table1
              GROUP BY ID
            UNION ALL
            SELECT dateadd(day,1,MAX(date)) date, ID, 0 quantity
              FROM table1
              GROUP BY ID
    )  AS U;
    SELECT p.ID, p.date prev_date, p.quantity prev_quantity, 
                 c.date curr_date, c.quantity curr_quantity
    FROM temp1 p INNER JOIN temp1 c ON c.rank = p.rank + 1 AND c.ID = p.ID
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.