?
Solved

Max Date in Web Intelligence

Posted on 2010-08-24
13
Medium Priority
?
5,888 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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