Simplify the problem, the following list,
Type number number sequence
A 10 10 1
A 10 10 2
10 10 3 B
A 10 20 4
10 10 5 B
10 20 6 B
B 10 30 7
A 10 40 8
10 10 9 B
A 10 20 10
Is the total number of days calculated, that is, to judge if the type is A type of A, so the total number of days is equal to the number of days
A type to B, then the total number of days=days + a day, and continues to judge one column type, such as
A or B, then the total number of days=+ number + up day by day number





Everyone a great god, and help to write an analysis of function of SQL,

Among actual business: as the servicing existing debt situation, principal will pay the service interval has all the interest principal repayments, for example, a fund 100 million, principal
Monthly payments, monthly pay 1 million, 50 million, in the amount of principal repayments if the day have to insert a service record, and the corresponding 50 million half month interest 250000,
At the end of 500000 remaining 50 million corresponding interest payments

Actual data: the following table according to breath day sorting, whether on the type of a data for debt, debt, if for the actual number of days=+ the number of days in a row, or equal to the number of days itself
Breath day pay a type number of the actual number of days amount of actual amount
2014/3/13 2017/4/10 payment date 1124, 1124, 200000000
2017/4/10 2017/4/29 debt, 19 19 100000000 200000000
2017/4/29 2017/5/26 debt day 27 46 50000000 100000000
2017/5/26 2018/4/10 payment date 319, 365, 50000000
2018/4/10 2019/3/13, 337, 337, 50000000, 50000000
For example: the first line type on the field data is empty, the actual number of days==1124
The second line type on the field data for the payment date, the actual number of days==19
Article 3 the line type on the field data for the debt, the actual number of days=27 days + line of the actual number of days=+ 19=46
Article 4 a line type on the field data for the debt, the actual number of days=+ line of the actual number of days=46=319 + 319 + 27 + 19=365

Build predicate sentence

 create table TEST99 
(
LX1 VARCHAR2 (10), 
VDATE DATE, 
MDATE DATE, 
TS NUMBER, 
HBJE NUMBER, 
AMT NUMBER 
) 

Data

 insert into test99 (LX1, VDATE MDATE, TS, HBJE, AMT) 
Values (' payment date 'to_date (' 13-03-2014', 'dd - mm - yyyy), to_date (' 10-04-2017', 'dd - mm - yyyy), 1124, 0, 200000000); 
Insert into test99 (LX1, VDATE MDATE, TS, HBJE, AMT) 
Values (' debt, 'to_date (' 10-04-2017', 'dd - mm - yyyy), to_date (' 29-04-2017', 'dd - mm - yyyy), 19, 100000000, 200000000); 
Insert into test99 (LX1, VDATE MDATE, TS, HBJE, AMT) 
Values (' debt, 'to_date (' 29-04-2017', 'dd - mm - yyyy), to_date (' 26-05-2017', 'dd - mm - yyyy), 27, 50000000, 100000000); 
Insert into test99 (LX1, VDATE MDATE, TS, HBJE, AMT) 
Values (' payment date 'to_date (' 26-05-2017', 'dd - mm - yyyy), to_date (' 10-04-2018', 'dd - mm - yyyy), 319, 0, 50000000); 
Insert into test99 (LX1, VDATE MDATE, TS, HBJE, AMT) 
Values (' maturity 'to_date (' 10-04-2018', 'dd - mm - yyyy), to_date (' 13-03-2019', 'dd - mm - yyyy), 337, 50000000, 50000000); 

CodePudding user response:

Rt may now use a lead or lad function

CodePudding user response:

Turn 11111

CodePudding user response:

Do not use vernier can be realized