SQLTeam.com | Weblogs | Forums

Previous row value


#1

Hi,

How Can I get the Previous row value from the following view? If the current Value is null then I must have the value of the previous row :

My Code :

SELECT DISTINCT dbo.orkrg.ordernr AS PO, dbo.orkrg.orddat AS PODate, dbo.orkrg.valcode AS POCur, dbo.orkrg.koers AS POXRate, dbo.rates.rate_exchange AS XRateD
FROM dbo.orkrg LEFT OUTER JOIN
dbo.rates ON dbo.orkrg.orddat = dbo.rates.date_l
WHERE (dbo.orkrg.ord_soort = 'B')

I Have This :

| PO | PO DATE | POCUR | POXRATE | XRATED |
50000 | 2013/05/20 | USD | 17.2530 | 17.2530 |
50001 | 2013/05/21 | MXN | 1.0000 | 17.2750 |
50002 | 2013/05/22 | USD | 17.3022 | NULL |

Condition : If XRateD Is Null Then It Must have the previous XRateD Value :

I Need This :

| PO | PO DATE | POCUR | POXRATE | XRATED |
50000 | 2013/05/20 | USD | 17.2530 | 17.2530 |
50001 | 2013/05/21 | MXN | 1.0000 | 17.2750 |
50002 | 2013/05/22 | USD | 17.3022 | 17.2550 |

Thanks in advance


#2

Try this:

select po
      ,podate
      ,pocur
      ,poxrate
      ,xrated
  from (select o.ordernr as po
              ,o.orddat as podate
              ,o.valcode as pocur
              ,o.koers as poxrate
              ,r.rate_exchange as xrated
              ,row_number() over(partition by o.ordernr order by r.date_l desc) as rn
          from dbo.orkrg as o
               left outer join dbo.rates as r
                            on r.date_l<=o.orddat
                           /*****
                            * For speed purpose, uncomment this if you want to limit looking
                            * one month back (or adjust to fit your needs)
                           and r.date_l>=dateadd(month,-1,o.orddat)
                            *
                            *****/
         where o.ord_soort='B'
       ) as a
 where rn=1
;

#3

Thank You so much bitsmed, Its running perfect.

Regards...