asked on 4/3/2020

SQL Query

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

Microsoft SQL Server

Last Comment

awking00

4/4/2020

PortletPaul

4/4/2020

Why?

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:

CREATE TABLE mytable(
   ID          INTEGER
  ,Guid        VARCHAR(7) NOT NULL
  ,LinkGuid    VARCHAR(7) NOT NULL
  ,LoanNumber  INTEGER  NOT NULL
  ,LienType    VARCHAR(11) NOT NULL
  ,ProgramCode VARCHAR(4) NOT NULL
);
INSERT INTO mytable(ID,Guid,LinkGuid,LoanNumber,LienType,ProgramCode) VALUES (1,'{f1244}','{2077a}',123456789,'First Lien','Bond');
INSERT INTO mytable(ID,Guid,LinkGuid,LoanNumber,LienType,ProgramCode) VALUES (2,'{2077a}','{f1244}',567345673,'Second Lien','C30');
INSERT INTO mytable(ID,Guid,LinkGuid,LoanNumber,LienType,ProgramCode) VALUES (3,'{4ertw}','{w234r}',342342342,'First Lien','C15');
INSERT INTO mytable(ID,Guid,LinkGuid,LoanNumber,LienType,ProgramCode) VALUES (4,'{w234r}','{4ertw}',567834566,'Second Lien','C30');


select 
t1.*, t2.ProgramCode t2ProgramCode
from mytable t1
left join mytable t2 on t1.guid = t2.linkguid
order by id

Open in new window

result:

+----+---------+----------+------------+-------------+-------------+---------------+
| ID |  Guid   | LinkGuid | LoanNumber |  LienType   | ProgramCode | t2ProgramCode |
+----+---------+----------+------------+-------------+-------------+---------------+
|  1 | {f1244} | {2077a}  |  123456789 | First Lien  | Bond        | C30           |
|  2 | {2077a} | {f1244}  |  567345673 | Second Lien | C30         | Bond          |
|  3 | {4ertw} | {w234r}  |  342342342 | First Lien  | C15         | C30           |
|  4 | {w234r} | {4ertw}  |  567834566 | Second Lien | C30         | C15           |
+----+---------+----------+------------+-------------+-------------+---------------+

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

4/4/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

arnold

4/4/2020

Not sure what your question really is or what you were looking for.

In general, one gets an answer to the question posed.

To get an encompassing answer from someone who has no knowledge or access to the underlying information requires a detailed context filled full of information and what you want.

The difficulty with providing answers to SQL topics, is that table structure, data might include proprietary information.

SQL query are specialized to a specific criteria, you can not query that applies to table1, table2 and apply it to tables A,b,and c.

You can use the information as some of us have to learn how to use the query slightwv provided you to address your prior issue to apply it to the new situation.

Input [sql query ] output.

Your prior question, you provided the input, desired output and slightwv provided you with the SQL

Now use that to determine what changes you need to make to implement the new query with the new input to get the new desired output.

The input might differ such that the same approach is not right.

My approach, the tables that contain your input, do they have related columns that are referential, or on whose basis rows in one table are related

This is commonly why experts commonly ask for the create table directives.

SOLUTION

arnold

4/4/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

awking00

4/4/2020

Can you provide sample data for both tables and the expected results from that data?