Avatar of phman1275
phman1275

asked on 

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

Avatar of undefined
Last Comment
awking00
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Blurred text
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
Avatar of arnold
arnold
Flag of United States of America image

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
Avatar of arnold
arnold
Flag of United States of America image

Blurred text
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.
Avatar of awking00
awking00
Flag of United States of America image

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

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo