By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL DROP TABLE #MyTable
create table #MyTable (PolNo varchar(50), ControlNo int, PrevControlNo int, Premim money)
insert into #MyTable values ('Policy-00', 5000, NULL, 1000),
('Policy-01', 6000, 5000, 3200),
('Policy-02', 7000, 6000, 4500),
('Policy-03', 8000, 7000, 3800)
select * from #MyTable
| PolNo | ControlNo | PrevControlNo | Premim |
|---|---|---|---|
| Policy-00 | 5000 | null | 1000.0000 |
| Policy-01 | 6000 | 5000 | 3200.0000 |
| Policy-02 | 7000 | 6000 | 4500.0000 |
| Policy-03 | 8000 | 7000 | 3800.0000 |
SELECT *
FROM #MyTable m1
LEFT JOIN #MyTable m2
ON m2.ControlNo = m1.PrevControlNo
| PolNo | ControlNo | PrevControlNo | Premim | PolNo | ControlNo | PrevControlNo | Premim |
|---|---|---|---|---|---|---|---|
| Policy-00 | 5000 | null | 1000.0000 | null | null | null | null |
| Policy-01 | 6000 | 5000 | 3200.0000 | Policy-00 | 5000 | null | 1000.0000 |
| Policy-02 | 7000 | 6000 | 4500.0000 | Policy-01 | 6000 | 5000 | 3200.0000 |
| Policy-03 | 8000 | 7000 | 3800.0000 | Policy-02 | 7000 | 6000 | 4500.0000 |