MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `tblregs1` (
`RegID` bigint(20) NOT NULL,
`RegRef` bigint(20) NOT NULL,
`RegCode` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`RegDate` date NOT NULL,
`RegCurr` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblregs1` (`RegID`, `RegRef`, `RegCode`, `RegDate`, `RegCurr`) VALUES
(1, 202010011140192, '', '2020-10-01', 1),
(2, 202010011141252, '', '2020-10-01', 1),
(3, 202010011141562, '', '2020-10-01', 1),
(4, 202010011143242, '', '2020-10-02', 0),
(5, 202010010103422, '', '2020-10-01', 1),
(6, 202010020836472, '', '2020-10-01', 0),
(7, 202010030101142, '', '2020-10-02', 0),
(8, 202010030101272, '', '2020-10-02', 0),
(9, 202010030101392, '', '2020-10-02', 0),
(10, 202010030101522, '', '2020-10-02', 0),
(11, 202010030102112, '', '2020-10-02', 0),
(12, 202010030153562, '', '2020-10-03', 1),
(14, 202010030158322, '', '2020-10-03', 1),
(15, 202010030431432, '', '2020-10-02', 0),
(16, 202010030439122, '', '2020-10-01', 1),
(17, 202010030512263, '', '2020-10-01', 1),
(18, 202010030515133, '', '2020-10-03', 2),
(19, 202010041255541, '', '2020-10-02', 1);

SELECT * FROM tblregs1;


RegID
RegRef
RegCode
RegDate
RegCurr


1
202010011140192

2020-10-01
1

2
202010011141252

2020-10-01
1

3
202010011141562

2020-10-01
1

4
202010011143242

2020-10-02
0

5
202010010103422

2020-10-01
1

6
202010020836472

2020-10-01
0

7
202010030101142

2020-10-02
0

8
202010030101272

2020-10-02
0

9
202010030101392

2020-10-02
0

10
202010030101522

2020-10-02
0

11
202010030102112

2020-10-02
0

12
202010030153562

2020-10-03
1

14
202010030158322

2020-10-03
1

15
202010030431432

2020-10-02
0

16
202010030439122

2020-10-01
1

17
202010030512263

2020-10-01
1

18
202010030515133

2020-10-03
2

19
202010041255541

2020-10-02
1


CREATE TABLE `tblregs2` (
`RegdID` bigint(20) NOT NULL,
`RegRef` bigint(20) NOT NULL,
`RegdM1` double NOT NULL DEFAULT '0',
`RegdD1` double NOT NULL DEFAULT '0',
`RegdAccID` int(11) NOT NULL,
`RegdDetails` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`RegdCurrPrice` double NOT NULL DEFAULT '0',
`RegdM2` double NOT NULL DEFAULT '0',
`RegdD2` double NOT NULL DEFAULT '0',
`RecStatus` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Normal',
`RegdNo` tinyint(4) NOT NULL,
`RegdMtbkRaseed` double NOT NULL DEFAULT '0',
`RegdMtbkFark` double NOT NULL DEFAULT '0',
`RegdMtbkStatus` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`RegdMtbkNotes` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `tblregs2` (`RegdID`, `RegRef`, `RegdM1`, `RegdD1`,      
`RegdAccID`, `RegdDetails`, `RegdCurrPrice`, `RegdM2`, `RegdD2`,     
`RecStatus`, `RegdNo`, `RegdMtbkRaseed`, `RegdMtbkFark`, `RegdMtbkStatus`, 
`RegdMtbkNotes`) VALUES
(1, 202010011140192, 1000, 0, 916, 'Des1', 1, 1000, 0, 'Normal', 0, 0, 0, 
NULL, NULL),
(4, 202010011141252, 0, 10000, 32, 'Des1', 1, 0, 10000, 'Normal', 2, 0, 0, 
NULL, NULL),
(13, 202010011143242, 1680, 0, 32, 'Des1', 1, 1680, 0, 'Normal', 3, -8320, 
0, 'no', 'no'),
(14, 202010011143242, 0, 1680, 15, 'Des1', 1, 0, 1680, 'Normal', 4, 0, 0, 
NULL, NULL),
(16, 202010011143242, 0, 10, 32, 'Des1', 1, 0, 10, 'Normal', 6, 0, 0, 
NULL, NULL),
(17, 202010011143242, 15, 0, 32, 'Des1', 1, 15, 0, 'Normal', 7, -8315, 0, 
'', '-8315'),
(19, 202010011143242, 450000, 0, 32, 'Des1', 1235, 364.37, 0, 'Normal', 9, 
0, 0, '', ''),
(20, 202010011143242, 0, 450000, 306, 'Des1', 1, 0, 450000, 'Normal', 10, 
0, 0, NULL, NULL),
(33, 202010020836472, 0, 0, 916, '', 0, 0, 0, 'Normal', 9, 0, 0, NULL, 
NULL),
(34, 202010020836472, 0, 0, 86, '', 0, 0, 0, 'Normal', 10, 0, 0, NULL, 
NULL),
(35, 202010030153562, 1400, 0, 32, 'Des1', 32, 1400, 0, 'Normal', 0, 
-6550.63, 0, '', '12'),
(36, 202010030153562, 0, 1400, 55, 'Des1', 55, 0, 1400, 'Normal', 0, 0, 0, 
NULL, NULL),
(38, 202010030158322, 0, 7000, 32, 'Des1', 1, 0, 7000, 'Normal', 2, 0, 0, 
NULL, NULL),
(64, 202010041255541, 0, 4500, 32, 'Des1', 1, 0, 4500, 'Normal', 2, 0, 0, 
NULL, NULL),
(65, 202010041255541, 10, 0, 33, 'Des1', 1, 10, 0, 'Normal', 3, 0, 0, 
NULL, NULL),
(66, 202010041255541, 0, 10, 45, 'Des1', 1, 0, 10, 'Normal', 4, 0, 0, 
NULL, NULL);

SELECT * FROM tblregs2;


RegdID
RegRef
RegdM1
RegdD1
RegdAccID
RegdDetails
RegdCurrPrice
RegdM2
RegdD2
RecStatus
RegdNo
RegdMtbkRaseed
RegdMtbkFark
RegdMtbkStatus
RegdMtbkNotes


1
202010011140192
1000
0
916
Des1
1
1000
0
Normal
0
0
0
null
null

4
202010011141252
0
10000
32
Des1
1
0
10000
Normal
2
0
0
null
null

13
202010011143242
1680
0
32
Des1
1
1680
0
Normal
3
-8320
0
no
no

14
202010011143242
0
1680
15
Des1
1
0
1680
Normal
4
0
0
null
null

16
202010011143242
0
10
32
Des1
1
0
10
Normal
6
0
0
null
null

17
202010011143242
15
0
32
Des1
1
15
0
Normal
7
-8315
0

-8315

19
202010011143242
450000
0
32
Des1
1235
364.37
0
Normal
9
0
0



20
202010011143242
0
450000
306
Des1
1
0
450000
Normal
10
0
0
null
null

33
202010020836472
0
0
916

0
0
0
Normal
9
0
0
null
null

34
202010020836472
0
0
86

0
0
0
Normal
10
0
0
null
null

35
202010030153562
1400
0
32
Des1
32
1400
0
Normal
0
-6550.63
0

12

36
202010030153562
0
1400
55
Des1
55
0
1400
Normal
0
0
0
null
null

38
202010030158322
0
7000
32
Des1
1
0
7000
Normal
2
0
0
null
null

64
202010041255541
0
4500
32
Des1
1
0
4500
Normal
2
0
0
null
null

65
202010041255541
10
0
33
Des1
1
10
0
Normal
3
0
0
null
null

66
202010041255541
0
10
45
Des1
1
0
10
Normal
4
0
0
null
null


SELECT d.RegDate, 
       d.RegdID,
       d.RegdM2, 
       d.RegdD2, 
       CAST(@s := @s + d.RegdM2-d.RegdD2 AS DECIMAL(10,2)) AS xresult
FROM (SELECT a.RegDate,
             b.RegdM2, 
             b.RegdD2, 
             b.RegdID
       FROM tblregs2 b
       INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
       WHERE b.RegdAccID=32 ) d
CROSS JOIN (SELECT @s := 0) c
ORDER BY d.RegDate, 
         d.RegdID;


RegDate
RegdID
RegdM2
RegdD2
xresult


2020-10-01
4
0
10000
-10000.00

2020-10-02
13
1680
0
-8320.00

2020-10-02
16
0
10
-8330.00

2020-10-02
17
15
0
-8315.00

2020-10-02
19
364.37
0
-7950.63

2020-10-02
64
0
4500
-12450.63

2020-10-03
35
1400
0
-11050.63

2020-10-03
38
0
7000
-18050.63


SELECT a.RegDate,
       b.RegdID,
       b.RegdM2, 
       b.RegdD2, 
       CAST(SUM(b.RegdM2-b.RegdD2) OVER (PARTITION BY RegdAccID 
                                         ORDER BY a.RegDate, b.RegdID) AS DECIMAL(10,2)) xresult
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
WHERE b.RegdAccID=32
ORDER BY a.RegDate,
         b.RegdID;


RegDate
RegdID
RegdM2
RegdD2
xresult


2020-10-01
4
0
10000
-10000.00

2020-10-02
13
1680
0
-8320.00

2020-10-02
16
0
10
-8330.00

2020-10-02
17
15
0
-8315.00

2020-10-02
19
364.37
0
-7950.63

2020-10-02
64
0
4500
-12450.63

2020-10-03
35
1400
0
-11050.63

2020-10-03
38
0
7000
-18050.63


SELECT b.RegdAccID,
       a.RegDate,
       b.RegdID,
       b.RegdM2, 
       b.RegdD2, 
       CAST(SUM(b.RegdM2-b.RegdD2) OVER (PARTITION BY RegdAccID 
                                         ORDER BY a.RegDate, b.RegdID) AS DECIMAL(10,2)) xresult
FROM tblregs2 b
INNER JOIN tblregs1 a ON b.RegRef = a.RegRef
ORDER BY b.RegdAccID,
         a.RegDate,
         b.RegdID;


RegdAccID
RegDate
RegdID
RegdM2
RegdD2
xresult


15
2020-10-02
14
0
1680
-1680.00

32
2020-10-01
4
0
10000
-10000.00

32
2020-10-02
13
1680
0
-8320.00

32
2020-10-02
16
0
10
-8330.00

32
2020-10-02
17
15
0
-8315.00

32
2020-10-02
19
364.37
0
-7950.63

32
2020-10-02
64
0
4500
-12450.63

32
2020-10-03
35
1400
0
-11050.63

32
2020-10-03
38
0
7000
-18050.63

33
2020-10-02
65
10
0
10.00

45
2020-10-02
66
0
10
-10.00

55
2020-10-03
36
0
1400
-1400.00

86
2020-10-01
34
0
0
0.00

306
2020-10-02
20
0
450000
-450000.00

916
2020-10-01
1
1000
0
1000.00

916
2020-10-01
33
0
0
1000.00

RegID	RegRef	RegDate	RegCurr
1	202010011140192	2020-10-01	1
2	202010011141252	2020-10-01	1
3	202010011141562	2020-10-01	1
4	202010011143242	2020-10-02	0
5	202010010103422	2020-10-01	1
6	202010020836472	2020-10-01	0
7	202010030101142	2020-10-02	0
8	202010030101272	2020-10-02	0
9	202010030101392	2020-10-02	0
10	202010030101522	2020-10-02	0
11	202010030102112	2020-10-02	0
12	202010030153562	2020-10-03	1
14	202010030158322	2020-10-03	1
15	202010030431432	2020-10-02	0
16	202010030439122	2020-10-01	1
17	202010030512263	2020-10-01	1
18	202010030515133	2020-10-03	2
19	202010041255541	2020-10-02	1

RegdID	RegRef	RegdM1	RegdD1	RegdAccID	RegdDetails	RegdCurrPrice	RegdM2	RegdD2	RecStatus	RegdNo	RegdMtbkRaseed	RegdMtbkStatus	RegdMtbkNotes
1	202010011140192	1000	0	916	Des1	1	1000	0	Normal	0	0	null	null
4	202010011141252	0	10000	32	Des1	1	0	10000	Normal	2	0	null	null
13	202010011143242	1680	0	32	Des1	1	1680	0	Normal	3	-8320	no	no
14	202010011143242	0	1680	15	Des1	1	0	1680	Normal	4	0	null	null
16	202010011143242	0	10	32	Des1	1	0	10	Normal	6	0	null	null
17	202010011143242	15	0	32	Des1	1	15	0	Normal	7	-8315		-8315
19	202010011143242	450000	0	32	Des1	1235	364.37	0	Normal	9	0
20	202010011143242	0	450000	306	Des1	1	0	450000	Normal	10	0	null	null
33	202010020836472	0	0	916		0	0	0	Normal	9	0	null	null
34	202010020836472	0	0	86		0	0	0	Normal	10	0	null	null
35	202010030153562	1400	0	32	Des1	32	1400	0	Normal	0	-6550.63		12
36	202010030153562	0	1400	55	Des1	55	0	1400	Normal	0	0	null	null
38	202010030158322	0	7000	32	Des1	1	0	7000	Normal	2	0	null	null
64	202010041255541	0	4500	32	Des1	1	0	4500	Normal	2	0	null	null
65	202010041255541	10	0	33	Des1	1	10	0	Normal	3	0	null	null
66	202010041255541	0	10	45	Des1	1	0	10	Normal	4	0	null	null

RegDate	RegdID	RegdM2	RegdD2	xresult
2020-10-01	4	0	10000	-10000.00
2020-10-02	13	1680	0	-8320.00
2020-10-02	16	0	10	-8330.00
2020-10-02	17	15	0	-8315.00
2020-10-02	19	364.37	0	-7950.63
2020-10-02	64	0	4500	-12450.63
2020-10-03	35	1400	0	-11050.63
2020-10-03	38	0	7000	-18050.63

RegdAccID	RegDate	RegdID	RegdM2	RegdD2	xresult
15	2020-10-02	14	0	1680	-1680.00
32	2020-10-01	4	0	10000	-10000.00
32	2020-10-02	13	1680	0	-8320.00
32	2020-10-02	16	0	10	-8330.00
32	2020-10-02	17	15	0	-8315.00
32	2020-10-02	19	364.37	0	-7950.63
32	2020-10-02	64	0	4500	-12450.63
32	2020-10-03	35	1400	0	-11050.63
32	2020-10-03	38	0	7000	-18050.63
33	2020-10-02	65	10	0	10.00
45	2020-10-02	66	0	10	-10.00
55	2020-10-03	36	0	1400	-1400.00
86	2020-10-01	34	0	0	0.00
306	2020-10-02	20	0	450000	-450000.00
916	2020-10-01	1	1000	0	1000.00
916	2020-10-01	33	0	0	1000.00