Solved

Max Date in Web Intelligence

Posted on 2010-08-24
13
5,334 Views
Last Modified: 2013-11-16
I'm using Web Intelligence (Business Objects XI 3.1) to build a query where I need to keep only the Max Comment Date for each Loan Number (need to remove duplicates).   What is the best way to go about this?  

I'm told I should use custom sql to get comment date equal to the max comment date for each loan, but everthing I've tried is not working.

For example, here is the data set when I run my query now.  There are duplicate loan numbers because there is more than one comment date.

Loan Number  |  Remove Duplicates |  Max Comment Date Open DR
0033523697           X                        6/22/2010
0033523697                              6/10/2009
0021844378           X                        8/3/2010
0021844378                              6/2/2010
0037867405           X                        6/30/2010
0037867405                              5/3/2010
0040627093           X                        7/14/2009

This is what I want to end up with:

Loan Number | Remove Duplicates | Max Comment Date Open DR
0033523697        X                      6/22/2010
0021844378        X                      8/3/2010
0037867405        X                      6/30/2010
0040627093        X                      7/14/2009

I've tried to use a variable to accomplish this, but the variable will only allow me to save as a measure, and you can't filter on a measure.   Here's the variable I built:

=If([Open DR].[Loan Number]=Previous([Open DR].[Loan Number]) And[Max Comment Date Open DR]>Previous([Max Comment Date Open DR]);"X";If([Open DR].[Loan Number]<>Previous([Open DR].[Loan Number]) And Not([Open DR].[Loan Number]=Previous([Open DR].[Loan Number]) And[Max Comment Date Open DR]>Previous([Max Comment Date Open DR]));"X"))

Open in new window


I've also tried the following SQL, but this returned no results:

SELECT
  BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  BDE.COMMENT_LOG_V.COMMENT_DATE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FROM
  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)
   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
  
WHERE
  (
   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  
   AND
   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_DATE = (SELECT MAX(BDE.COMMENT_LOG_V.COMMENT_DATE) from BDE.COMMENT_LOG_V)
  )
FOR FETCH ONLY WITH UR

Open in new window


I've also tried this SQL, but it returned the same results with the duplicates:

SELECT
  BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  MAX(BDE.COMMENT_LOG_V.COMMENT_DATE) as MAX_COMMENT_DATE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FROM
  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)
   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)
  
WHERE
  (
   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  
   AND
   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )
   AND
   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

  )

GROUP BY
BDE.BORROWER_V.LOAN_NUMBER,
  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,
  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,
  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,
  BDE.LOAN_V.MAN_CODE,
  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,
  BDE.TASK_TRACKING_V.TASK_ID,
  BDE.TASK_TRACKING_V.TASK_START_DATE,
  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,
  BDE.COMMENT_LOG_V.COMMENT_CODE,
  BDEBI.LOAN_STATUS.LOAN_STATUS,
  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE
FOR FETCH ONLY WITH UR

Open in new window


0
Comment
Question by:prodempsey
  • 7
  • 5
13 Comments
 

Expert Comment

by:bharat_gr8
ID: 33511608
How about using the DISTINCT keyword? Did you try that out prodempsey? Sorry, if that doesnt work. Will come up with more ideas.
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33511673
I'm not familiar with the DISTINCT keyword, can you elaborate?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33511680
well, your sql is kind of long, so i'll give you the solution for the simple case and you will integrate it into yours
if you have a table with these columns

Loan Number  |  Remove Duplicates |  Max Comment Date Open DR

you do

select LoanNumber  ,  RemoveDuplicates ,  MaxCommentDateOpenDR
from (select LoanNumber  ,  RemoveDuplicates ,  MaxCommentDateOpenDR,
  row_number() over(partition by LoanNumber order by MaxCommentDateOpenDR desc ) rown
  from your_table_name
) t
where rown = 1
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33512177
Momi_sabg,
I tried to itegrate the sql, but I got the following error:
"An unexpected token "WHERE" was found following ") t where rown = 1".  Expected tokens may include:  "AND".  SQLSTATE=42601. (WIS 10901)

Attached is the SQL I integrated:
 

SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





WHERE

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33512258
you wrote "where" twice,
try


SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   BDE.COMMENT_LOG_V.COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33513855
I used the updated SQL, but now I'm getting an error that say's:

"BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE" is not valid in the context where it is used.  SQLSTATE=42703. (WIS 10901)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 33514019
you need to fix the table aliases

SELECT

  BDE.LOAN_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33514873
I'm not very good with SQL, can you tell me what needs to be fixed on the table aliases?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33514953
on your original query, you referenced column of tables by thier names, for example
BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE

so BDE is the schema, TASK_TRACKING_V is the table and TASK_ACTUAL_CLOSE_DATE is the column

since we nested the entire query inside the () so we can use the row_number functions, db2 does know these names anymore, and it only knows the name t (which we specified after the closing the inner expression)
so you can't use BDE.TASK_TRACKING_V anymore,
as far as db2 concerns, only t exists
0
 
LVL 6

Author Comment

by:prodempsey
ID: 33515913
Is this what you mean.  The SQL appears to be without errors.  I'm running the query now, and I'll let you know if it gives me the correct results.
SELECT

t.LOAN_NUMBER,

t.MORTGAGOR_LAST_NAME,

t.FIRST_PRINCIPAL_BALANCE,

t.NEXT_PAYMENT_DUE_DATE,

t.MAN_CODE,

t.TASK_CONTACT_ID,

t.TASK_ID,

t.TASK_START_DATE,

t.TASK_ACTUAL_CLOSE_DATE,

t.COMMENT_CODE,

t.COMMENT_DATE,

t.LOAN_STATUS,

t.MSP_LAST_RUN_DATE

FROM

(SELECT BDE.LOAN_V.LOAN_NUMBER,BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.FIRST_PRINCIPAL_BALANCE,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_CONTACT_ID,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  BDE.COMMENT_LOG_V.COMMENT_CODE,

  BDE.COMMENT_LOG_V.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE,

 row_number() over(partition by BDE.LOAN_V.LOAN_NUMBER order by BDE.COMMENT_LOG_V.COMMENT_DATE desc ) rown

FROM

  BDE.COMMENT_LOG_V INNER JOIN BDE.LOAN_V ON (BDE.COMMENT_LOG_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER) 

) t

where rown = 1 





and

  (

   TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   AND

   COMMENT_CODE  In  ( 'REFNRF','REFTHL','REFSBI','REFC6W','REFE78','REFC68','REFH86','REFLIW','REFMEN','REFANO','REFGAT','REFCM9','REFNCN','REFBEI','REFNDC','REFLGL','REFPJK','REFD3L','REFCBD','REF73A','REFTPM','REFTEM','REFNC1','REFHRI','REFND1','REFLG1','REF0D3','REFOD3','REFU09','REFNY1','REFJDC','REFU25','REFAVD','REFCCJ','REFK8C','REFGJW','REFMNE','REFU59','REF36M','REFQ56','REFK9C','REFI8V','REF364','REFKHO','REF48V','REFL8V','REF18V','REFP83'  )

   AND

   COMMENT_DATE  Is Not Null  

  )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 6

Author Comment

by:prodempsey
ID: 33516004
The query returned no results.   Couldn't I do an inner Select of the Loan_Number and Max Comment Date – then join it into my main query.  If so, how do I do that?
0
 
LVL 6

Accepted Solution

by:
prodempsey earned 0 total points
ID: 33533190
Thanks for all your help, but this SQL worked for me that I received from a co-worker:


SELECT

  BDE.BORROWER_V.LOAN_NUMBER,

  BDE.BORROWER_V.MORTGAGOR_LAST_NAME,

  BDE.LOAN_V.NEXT_PAYMENT_DUE_DATE,

  BDE.LOAN_V.MAN_CODE,

  BDE.TASK_TRACKING_V.TASK_ID,

  BDE.TASK_TRACKING_V.TASK_START_DATE,

  BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE,

  A.COMMENT_CODE,

  A.COMMENT_DATE,

  BDEBI.LOAN_STATUS.LOAN_STATUS,

  BDEBI.LOAN_STATUS.MSP_LAST_RUN_DATE

FROM

   (

   SELECT  CL.*

FROM    BDE.COMMENT_LOG_V CL

        INNER JOIN (

            SELECT  LOAN_NUMBER, MAX(COMMENT_DATE) AS COMMENT_DATE,

                    MAX(COMMENT_TIME) AS COMMENT_TIME

            FROM    BDE.COMMENT_LOG_V

            WHERE   COMMENT_CODE IN ('REFNRF', 'REFTHL', 'REFSBI', 'REFC6W', 'REFE78', 'REFC68',

                        'REFH86', 'REFLIW', 'REFMEN', 'REFANO', 'REFGAT', 'REFCM9', 'REFNCN',

                        'REFBEI', 'REFNDC', 'REFLGL', 'REFPJK', 'REFD3L', 'REFCBD', 'REF73A',

                        'REFTPM', 'REFTEM', 'REFNC1', 'REFHRI', 'REFND1', 'REFLG1', 'REF0D3',

                        'REFOD3', 'REFU09', 'REFNY1', 'REFJDC', 'REFU25', 'REFAVD', 'REFCCJ',

                        'REFK8C', 'REFGJW', 'REFMNE', 'REFU59', 'REF36M', 'REFQ56', 'REFK9C',

                        'REFI8V', 'REF364', 'REFKHO', 'REF48V', 'REFL8V', 'REF18V', 'REFP83')

            GROUP BY LOAN_NUMBER

                    ) MCD ON (CL.LOAN_NUMBER = MCD.LOAN_NUMBER AND CL.COMMENT_DATE = MCD.COMMENT_DATE AND CL.COMMENT_TIME = MCD.COMMENT_TIME)

WHERE   CL.COMMENT_CODE IN ('REFNRF', 'REFTHL', 'REFSBI', 'REFC6W', 'REFE78', 'REFC68',

                        'REFH86', 'REFLIW', 'REFMEN', 'REFANO', 'REFGAT', 'REFCM9', 'REFNCN',

                        'REFBEI', 'REFNDC', 'REFLGL', 'REFPJK', 'REFD3L', 'REFCBD', 'REF73A',

                        'REFTPM', 'REFTEM', 'REFNC1', 'REFHRI', 'REFND1', 'REFLG1', 'REF0D3',

                        'REFOD3', 'REFU09', 'REFNY1', 'REFJDC', 'REFU25', 'REFAVD', 'REFCCJ',

                        'REFK8C', 'REFGJW', 'REFMNE', 'REFU59', 'REF36M', 'REFQ56', 'REFK9C',

                        'REFI8V', 'REF364', 'REFKHO', 'REF48V', 'REFL8V', 'REF18V', 'REFP83')

   ) A

   INNER JOIN BDE.LOAN_V ON (A.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.BORROWER_V ON (BDE.BORROWER_V.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

   INNER JOIN BDE.TASK_TRACKING_V ON (BDE.LOAN_V.LOAN_NUMBER=BDE.TASK_TRACKING_V.LOAN_NUMBER)

   INNER JOIN BDEBI.LOAN_STATUS ON (BDEBI.LOAN_STATUS.LOAN_NUMBER=BDE.LOAN_V.LOAN_NUMBER)

  

WHERE

  (

   BDE.TASK_TRACKING_V.TASK_ACTUAL_CLOSE_DATE  Is Null  

   AND

   BDE.TASK_TRACKING_V.TASK_ID  In  ( 'SSLGR2','SSLGR3','SSLGRF'  )

   )

FOR FETCH ONLY WITH UR

Open in new window

0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33536459
how is it in terms of performance?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now