this code from "slightwv" expert seems work only for the following table
select guid, linkguid, loannumber, lientype, case when linkguid=prev_guid then prev_code else programcode end new_programcode
from (
select guid, linkguid, loannumber, lientype, programcode,
lag(guid) over(order by loannumber asc) prev_guid,
lag(programcode) over(order by loannumber asc) prev_code
from junk
) x;
Example:
Guid LinkGuid LoanNumber LienType ProgramCode
{f1244} {2077a} 123456789 First Lien Bond
{2077a} {f1244} 567345673 Second Lien C30
Result
Guid LinkGuid LoanNumber LienType ProgramCode
{f1244} {2077a} 123456789 First Lien Bond
{2077a} {f1244} 567345673 Second Lien Bond <-- should change to 'Bond'
if the table like the following then the above code won't work.
Example:
Guid LinkGuid LoanNumber LienType ProgramCode
{f1244} {2077a} 123456789 First Lien Bond
{2077a} {f1244} 567345673 Second Lien C30
{4ertw} {w234r} 342342342 First Lien C15
{w234r} {4ertw} 567834566 Second Lien C30
Result
Guid LinkGuid LoanNumber LienType ProgramCode
{f1244} {2077a} 123456789 First Lien Bond
{2077a} {f1244} 567345673 Second Lien Bond <-- should change to 'Bond'
{4ertw} {w234r} 342342342 First Lien C15
{w234r} {4ertw} 567834566 Second Lien C30 <-- no change
Guid LinkGuid LoanNumber LienType ProgramCode
{f1244} {2077a} 123456789 First Lien Bond
{2077a} {f1244} 567345673 Second Lien Bond <-- why does this one change?
{4ertw} {w234r} 342342342 First Lien C15
{w234r} {4ertw} 567834566 Second Lien C30 <-- why does this one NOT change?
To help you we need to understand the required logic. Please try to explain why they are different.
This is NOT a solution, but it may help:
Open in new window
result:
Open in new window