Solved

Max Date in Web Intelligence

Posted on 2010-08-24
13
5,609 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2008 report on-load event 2 133
SSRS show background behind table 5 66
How to create a stored procedure in AS400 1 226
DB2 SQL How to complete the pivoting of this data. 5 77
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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