Compare the amount with previous timeline

Question

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

Answer 1

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

Answer 2

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".

Answer 3

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