How to insert 2nd row from the same table to 1st row in SQL Server

Deein S 1 Reputation point
2022-05-07T13:31:56.223+00:00

Hi Everyone,

I have a table as below in my sql server,
199887-screen-shot-2022-05-07-at-112140-pm.png

and I am aiming to get the below result, where the current 'resale date' is next 'sale date' of each item

199867-screen-shot-2022-05-07-at-112146-pm.png

I tried to use left join which joins the minimum of sale date with the main table based on ID, however, I am getting the below result.

199868-screen-shot-2022-05-07-at-112156-pm.png

I want to avoid the cases like highlighted yellow rows where each item is getting all the resale dates which is greater than the current sale date.

It would be great if anyone can help me with this. Thanks in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,318 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.5K Reputation points
    2022-05-07T13:54:23.633+00:00

    To start with, for these type of questions, we always recommend that you post the CREATE TABLE statement for your table(s) and INSERT statements with the sample data. We also need the expected result given the sample, but that be provided as a image. The point with CREATE TABLE + INSERT is that we can copy and paste into a query window to develop a tested solution. We cannot copy from images.

    The gist of this introduction is that you see below is an untested solution.

    WITH numbering AS (
       SELECT CustomerID, SaleDate, SaleValue,
                    rowno = row_number() OVER(PARTITION BY CustomerID ORDER BY SaleDate)
       FROM   tbl
    )
    SELECT CustomerID, 
          MIN(CASE WHEN rowno = 1 THEN SaleDate END) AS SaleDate,
          MIN(CASE WHEN rowno = 1 THEN SaleValue END) AS SaleValue,
          MIN(CASE WHEN rowno = 2 THEN SaleDate END) AS ReSaleDate,
          MIN(CASE WHEN rowno = 2 THEN SaleValue END) AS ReSaleValue
    FROM  numbering
    WHERE rowno IN (1, 2)
    GROUP  BY CustomerID
    
    0 comments No comments

  2. Naomi 7,366 Reputation points
    2022-05-08T02:23:41.123+00:00

    Try the following:

    ;with cte as (select Customer, [Sale Date], [Sale Value], LEAD([Sale Date]) over (partition by Customer order by [Sale Date]) as [Re-Sale Date], LEAD([Sale Value]) over (partition by Customer order by [Sale Date])) as  [Re-Sale Value]
    from Sales)
    
    select Customer, [Sale Date], ISNULL(FORMAT([Re-Sale Date], 'd/M/yy'), 'No Resale') as [Re-Sale Date], ISNULL([Re-Sale Value]),0) as [Re-Sale Value] from cte
    

    The above is untested.

    0 comments No comments

  3. Jingyang Li 5,891 Reputation points
    2022-05-09T02:09:38.3+00:00
    Create table atable   (
    CustomerID varchar(2),
    SaleDate date,
    SaleValue int
    
    ) 
    insert into atable (CustomerID,SaleDate,SaleValue)
    values('AA','2016-05-10',100)
    ,('BB','2016-05-20',123)
    ,('CC','2016-05-20',546)
    ,('AA','2018-07-30',33)
    ,('BB','2016-09-28',245)
    ,('AA','2022-05-01',20)
    
    
    ;with mycte as (
    Select * , row_number()
    Over(partition by CustomerID   Order by SaleDate ) rn
    from atable
    )
    
    Select m1.CustomerID [Customer ID]
    ,Format(m1.SaleDate,'d/M/yy') [Sale Date]
    ,m1.SaleValue [Sale Value]
    ,ISNULL(Format(m2.SaleDate,'d/M/yy'),'No ReSale') [Re-Sale Date]
    ,ISNULL(m2.SaleValue,0) [Re-Sale Value]
    
    from mycte m1 
    left join mycte m2 on m1.CustomerID=m2.CustomerID 
    and m1.rn=m2.rn-1
    order by m1.CustomerId, m1.SaleDate
    
    
    
    
    
    drop table atable
    
    0 comments No comments

  4. LiHong-MSFT 10,046 Reputation points
    2022-05-09T03:00:04.26+00:00

    Hi @Deein S
    To match the value of the next row, you can use the LEAD function to solve this issue.(Please Refer to this article for more details and examples of LEAD function: SQL Server LEAD Function)
    Check this query:

    CREATE TABLE #TEST(CustomerID VARCHAR(2),SaleDate DATE,SaleValue INT)  
    INSERT INTO #TEST VALUES  
    ('AA','2016-5-10',100),  
    ('BB','2016-5-20',123),  
    ('CC','2016-5-20',546),  
    ('AA','2018-7-30',33),  
    ('BB','2019-9-28',245),  
    ('AA','2022-5-1',20)  
      
    SELECT CustomerID,FORMAT(SaleDate,'d/M/yy')SaleDate,SaleValue  
          ,ISNULL( FORMAT(LEAD(SaleDate,1,NULL)OVER(PARTITION BY CustomerID ORDER BY SaleDate) ,'d/M/yy' ) ,'No ReSale' )AS [Re-Sale Date]  
    	  ,LEAD(SaleValue,1,0)OVER(PARTITION BY CustomerID ORDER BY SaleDate)AS [Re-Sale Value]   
    FROM #TEST  
    

    Output:
    200074-image.png

    Documentations about other functions:
    (1) FORMAT (Transact-SQL);
    (2) ISNULL (Transact-SQL);

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments