Compare the amount with previous timeline

Mike 341 Reputation points
2022-02-01T22:15:04.26+00:00

I have to compare the profit and Loss amount of the current timeline with the very previous current timeline. The result should show if proft and Loss data is different of current timeline with the very previous timeline from current timeline. The key field of this table is R1,TP,O1,Date1.

Create table #R1 (CL char(10), R1 char(10), TP char(10), O1 char(10), Date1 datetime, amount1 numeric(10,2), profit int , Loss int)

Insert into #R1 values ('111','AA','AAAA','11','2021-01-01', 12.35,1000, 10)
Insert into #R1 values ('111','AA','AAAA','11','2022-01-01', 15.55,1000, 10)

Insert into #R1 values ('111','RA','AAAA','11','2021-01-01', 16.99,1000, 10)
Insert into #R1 values ('111','RA','AAAA','11','2022-01-01', 17.99,500, 10)

Insert into #R1 values ('111','AB','AAAA','11','2021-01-01', 12.35,1000, 10)
Insert into #R1 values ('111','AB','AAAA','11','2022-01-01', 15.55,1000, 10)

Insert into #R1 values ('111','BA','AAAA','11','2021-01-01', 16.99,1000, 10)
Insert into #R1 values ('111','BA','AAAA','11','2022-01-01', 17.99,500, 9)

Select * from #R1 order by r1, Tp, o1, Date1

Desired output:
CL R1 TP O1 Date1 amount1 profit Loss
111 BA AAAA 11 2022-01-01 17.99 500 9
111 RA AAAA 11 2022-01-01 17.99 500 10

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

Accepted answer
  1. Viorel 112.9K Reputation points
    2022-02-02T06:09:11.937+00:00

    Check one of solutions:

    ;
    with Q as
    (
        select *,
            lag(profit) over (partition by CL, R1, TP, O1 order by Date1) prev_profit,
            lag(loss) over (partition by CL, R1, TP, O1 order by Date1) prev_loss,
            lead(Date1) over (partition by CL, R1, TP, O1 order by Date1) next_Date
        from #R1
    )
    select CL, R1, TP, O1, Date1, amount1, profit, Loss
    from Q
    where next_Date is null and (prev_profit <> profit or prev_loss <> loss)
    order by r1, Tp, o1, Date1
    
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2022-02-02T08:41:20.463+00:00

    Hi @Mike ,

    I think showing the difference will better to make a comparison. Please check below T-SQL;

    SELECT * FROM #R1 AS a  
    WHERE  
      EXISTS  
      (  
        SELECT* FROM  
          #R1 AS b  
        WHERE  
          a.R1 = b.R1  
          AND (a.profit <> b.profit or a.Loss <> b.Loss)  
      )  
    

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    1 person found this answer helpful.
    0 comments No comments

  2. Kenny Gua 411 Reputation points
    2022-02-02T14:28:46.773+00:00

    Hi Cathyji-msft, Your current output is CORRECT from query but I said current timeline (max(date1)) should compare data with previous timeline of (max(date1).
    If I add the following rows of 2020 then these rows will also be showing in the result which I don't. Only want current (maxdate1) row and previous rows in the result if have difference in profit or Loss data. The key field of this table is R1,TP,O1,Date1.

    Insert into #R1 values ('111','RA','AAAA','11','2020-01-01', 18.99,1000, 10)
    Insert into #R1 values ('111','AB','AAAA','11','2020-01-01', 14.35,1000, 10)

    Expected result:
    CL R1 TP O1 Date1 amount1 profit Loss
    111 RA AAAA 11 2021-01-01 16.99 1000 10
    111 RA AAAA 11 2022-01-01 17.99 500 10
    111 BA AAAA 11 2021-01-01 16.99 1000 10
    111 BA AAAA 11 2022-01-01 17.99 500 9

    0 comments No comments