# Compare the amount with previous timeline

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

1. 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. 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)
)
``````

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