need_solution
asked on
need to get status from the previous row
i have a resultset in sql with mth, employee no., status and order.
mth emp# status order
1/1/2012 12345 AA 2
2/1/2012 12345 AA 2
3/1/2012 12345 AA 2
. . . .
. . . .
. . . .
1/1/2014 12345 termed 4
2/1/2014 12345 termed 4
. . . .
. . . .
6/1/2015 12345 BB 5
. . . .
. . . .
12/1/2015 12345 termed 6
So, whenever status = termed i need to display status from the previous row, otherwise display the same row.
So solution should be something like this:
mth emp# status order status1
1/1/2012 12345 AA 2 AA
2/1/2012 12345 AA 2 AA
3/1/2012 12345 AA 2 AA
. . . .
. . . .
. . . .
1/1/2014 12345 termed 4 AA
2/1/2014 12345 termed 4 AA
. . . .
. . . .
6/1/2015 12345 BB 5 BB
. . . .
. . . .
12/1/2015 12345 termed 6 BB
I tried using partition by row, but since my statorders have intermittent values, can not really use that feature.
Thanks in advance.
mth emp# status order
1/1/2012 12345 AA 2
2/1/2012 12345 AA 2
3/1/2012 12345 AA 2
. . . .
. . . .
. . . .
1/1/2014 12345 termed 4
2/1/2014 12345 termed 4
. . . .
. . . .
6/1/2015 12345 BB 5
. . . .
. . . .
12/1/2015 12345 termed 6
So, whenever status = termed i need to display status from the previous row, otherwise display the same row.
So solution should be something like this:
mth emp# status order status1
1/1/2012 12345 AA 2 AA
2/1/2012 12345 AA 2 AA
3/1/2012 12345 AA 2 AA
. . . .
. . . .
. . . .
1/1/2014 12345 termed 4 AA
2/1/2014 12345 termed 4 AA
. . . .
. . . .
6/1/2015 12345 BB 5 BB
. . . .
. . . .
12/1/2015 12345 termed 6 BB
I tried using partition by row, but since my statorders have intermittent values, can not really use that feature.
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I saw that you did the right thing and opened a new question.
I will answer there.
I will answer there.
ASKER
You helped me in finding a solution to coding question i posted last week. The solution given by you worked so Thanks a lot for that. But my client came back and said they need a change in the logic here's how.
there is another field in our database estatus, my client wants me to use the estatus field instead of the status field. some of the status codes from the estatus field are active and some are inactive. As long as I see the active
estatus i should display as it is in the estatus1 field. If there is an inactive code, i should replace that with the prev active estatus and display in the estatus1 field, till i see the next active status. This is how I want
current result set:
mth emp# estatus order
1/1/2012 12345 AA 2
2/1/2012 12345 AA 2
3/1/2012 12345 AA 2
. . . .
. . . .
. . . .
1/1/2014 12345 BB 4
2/1/2014 12345 BB 4
. . . .
. . . .
6/1/2015 12345 CC 5
. . . .
. . . .
12/1/2015 12345 DD 6
1/1/2016 12345 EE 7
Here AA and DD are the active codes and BB, CC and EE are inactive codes. Unfortunately, there is no Active/Inactive flag which can be used.
this is what I need:
mth emp# estatus order estatus1
1/1/2012 12345 AA 2 AA
2/1/2012 12345 AA 2 AA
3/1/2012 12345 AA 2 AA
. . . .
. . . .
. . . .
1/1/2014 12345 BB 4 AA
2/1/2014 12345 BB 4 AA
. . . .
. . . .
6/1/2015 12345 CC 5 AA
. . . .
. . . .
12/1/2015 12345 DD 6 DD
1/1/2016 12345 EE 7 DD
Thanks in advance,