By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `Report` (
`empid` int(11) DEFAULT NULL,
`effectivefrom` text,
`totalSalary` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO Report
(`empid`,
`effectivefrom`,
`totalSalary`)
VALUES
(200,'2017-07-01',10000
);
INSERT INTO Report
(`empid`,
`effectivefrom`,
`totalSalary`)
VALUES
(200,'2017-07-01',12000
);
INSERT INTO Report
(`empid`,
`effectivefrom`,
`totalSalary`)
VALUES
(200,'2017-07-01',15000
);
select r.totalSalary, r.nextTotalSalary , ( case when isnull(r.nextTotalSalary)= 1 then r.totalSalary
else nextTotalSalary
end ) - r.totalSalary
from (
| totalSalary | nextTotalSalary | ( case when isnull(r.nextTotalSalary)= 1 then r.totalSalary else nextTotalSalary end ) - r.totalSalary |
|---|---|---|
| 10000 | 12000 | 2000 |
| 12000 | 15000 | 3000 |
| 15000 | null | 0 |