Compare and subtract the records 2 Table

Question

i have 2 tables (each two tables have the same structure) i get the sum of two table with bellow code. now i want to find Sum of the records from the previous month and Sum of the current month, and Compare they. If the sum of the previous month is greater than the sum of the current month Records to be selected.If not, is not selected

Table 1                                        Table 2<br>
StudentId=1  Score=50 Date =2017/01/01      StudentId= 1 Score=100 Date =2017/01/01<br>
StudentId=1  Score=20 Date =2017/02/01      StudentId= 1 Score=10 Date =2017/02/01<br>
StudentId=2  Score=60 Date =2017/01/01      StudentId= 2 Score=100 Date =2017/01/01<br>
StudentId=2  Score=540 Date =2017/02/01     StudentId= 2 Score=100 Date =2017/02/01<br>

Current Result:

StudentId       HighScoreUser<br>
1               180<br>
2               800<br>
---------------------------------<br>

Result that i want :

StudentId   Prev Month(2017/01/01)  Current Month(2017/02/01)<br>
1           150                     30<br>
2           160                     640<br>

1 --> 150 > 30 -->True? --> Yes, So must be selected

2 --> 160 > 640 -->True? --> No, So must be not selected

Result (selected values) = StudentId , (Sum prev Month - Sum Current Month)

<br>`CREATE PROCEDURE SelectTopMonth
    @Date1  NVARCHAR(12),
    @Date2  NVARCHAR(12)
AS
    SELECT StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint     WHERE Date >= @Date1    AND     Date <= @Date2
      UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint     WHERE Date >= @Date1    AND     Date <= @Date2
     ) as T 
GROUP BY  StudentId ORDER BY HighScoreUser DESC
RETURN 0`

Answer 1

You can use lead analytical function of SQL Server in order to get your result.

TABLE1

create table table1 
(
  studentid integer,
  score integer,
  date date
);

insert into table1 values(1,50,'2017/01/01'); 
insert into table1 values(1,20,'2017/02/01');
insert into table1 values(2,60,'2017/01/01');
insert into table1 values(2,540,'2017/02/01');  

TABLE2

create table table2
(
  studentid integer,
  score integer,
  date date
);
insert into table2 values(1,100,'2017/01/01'); 
insert into table2 values(1,10,'2017/02/01');
insert into table2 values(2,100,'2017/01/01');
insert into table2 values(2,100,'2017/02/01');  

Query:

with data  as (
select table1.studentid , (table1.score + table2.score) as pre_score, table1.date,
lead((table1.score + table2.score),1) over(partition by table1.studentid order by table1.studentid,table1.date) 
as cur_score
from table1 join table2 
on table1.studentid = table2.studentid 
and table1.date = table2.date
)
select * from data
where cur_score is not null 
and pre_score > cur_score

Added this condition as per your comment "and pre_score > cur_score".