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

Question

Hi Everyone,

I have a table as below in my sql server,

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

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.

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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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:

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.