lundi 16 novembre 2015

Fetching column values from result of case expression in sql

I have a table with columns PRNumber, PRLevel, Levelstatus and TransDate
Executed the following case expression,
Firstcase:

 select PRNumber,PRLevel,Levelstatus,Trasdate, 
    case when Levelstatus = 'PUR' 
    then prlevel - 1 
    end as [PR_level] 
from dbo.tblpr_approvalstatus where prnumber = '000005'

PRNumber    PRLevel Levelstatus Trasdate                  PR_Level
000005      1       DM          2015-06-15 10:07:33.467   NULL
000005      2       PRE         2015-06-15 11:09:08.980   NULL
000005      4       IMM         2015-06-19 12:02:56.600   NULL
000005      5       PUR         2015-06-19 14:37:43.650   4
000005      3       FIN         2015-06-18 21:07:57.933   NULL

When Levelstatus is equal to PUR, I get the PrLevel 5 from case expression, I want to fetch the Trasdate of previous value of PRlevel in the case expression like as below
Secondcase:

select case when PRlevel = 4 
then trasdate 
end as [Transaction_date] 
from tblpr_approvalstatus where prnumber = '000005'

I want to combine first case and second case expression as a single expression to fetch the Trasdate column values when Levelstatus = ‘PUR’ and previous value of PRLevel.
The columns PRLevel, Levelstatus and TransDate are having different values for each PRNumber.

Aucun commentaire:

Enregistrer un commentaire