question

DeeinS-6009 avatar image
0 Votes"
DeeinS-6009 asked LiHongMSFT-3908 commented

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

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-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DeeinS-6009
Have you validate all the answers so far and provide any update?
If all of the answers are not working or helpful, please share with us your confusion or more details about this issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered NaomiNNN edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @DeeinS-6009
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.


image.png (6.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.