Shown Previous row value into next row value such condition

snehassis 0
Viewed 27

My table is below:

TranType | Date      | ItemID| Rate |
---------|-----------|-------| -----|
PUR      |2021-04-01 | 1     |  16  |
SAL      |2021-04-05 | 1     |   0  |
SAL      |2021-04-06 | 1     |   0  |
SAL      |2021-04-16 | 1     |   0  |
SAL      |2021-04-19 | 1     |   0  |
PUR      |2021-04-23 | 1     |  18  |
SAL      |2021-04-26 | 1     |   0  |
SAL      |2021-04-27 | 1     |   0  |
PUR      |2021-04-28 | 1     |  20  |
SAL      |2021-05-07 | 1     |  0   |

I want to out put my result as shown below:

TranType | Date      | ItemID| Rate |
---------|-----------|-------| -----|
PUR      |2021-04-01 | 1     |  16  |
SAL      |2021-04-05 | 1     |  16  |
SAL      |2021-04-06 | 1     |  16  |
SAL      |2021-04-16 | 1     |  16  |
SAL      |2021-04-19 | 1     |  16  |
PUR      |2021-04-23 | 1     |  18  |
SAL      |2021-04-26 | 1     |  18  |
SAL      |2021-04-27 | 1     |  18  |
PUR      |2021-04-28 | 1     |  20  |
SAL      |2021-05-07 | 1     |  20  |

How can I solve above problem in any SQL database version. Please help me.

sql-server sql
1 Answers

Please try below script,

SELECT TranType
      ,Date
      ,ItemID
      ,Rate
      ,(SELECT TOP 1 Rate FROM #T TT WHERE Rate <>0 AND T.Date  >= TT.Date ORDER BY Date DESC)
FROM #T T

If data is huge should check the performance of the query.

Srinivasan Rajasekaran0
Related