troubleshooting Question

SQL Query

Avatar of phman1275
phman1275 asked on
Microsoft SQL Server
5 Comments1 Solution58 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros