I inherited this report. Its way above my head!!

Question

Post reply

I inherited this report. Its way above my head!!

davidskreczko

Grasshopper

Points: 22
More actions
October 15, 2010 at 9:36 am

#230525

Hello SQL Gods!
I inherited this report. I just started to learn SQL, and it’s a way above my head. I need to fix the query so that “mwlApprovalStatus.StatusDateTime” is selecting the dates with the PA-APPLREC Code, which it is already doing.
But I also need a field it to give me the date with the StatusMWcode = RECEIVED Date. And call it DC:RecDate.
Right now it’s only pulling the earliest date per LoanApp_ID
thanks for the help
Sample Data
LoanApp_ID______StatusMWCode_______StatusDateTime
A1737............... ..APPROVED.................3/5/10
A1737..................SCHEDULED................ 3/12/10
A1737..................PA-APPRV............... 1/20/10
A1737..................PA-APPLREC...............1/12/10
A1737................. SUBMITTED............... 3/5/10
A1737................. FUNDING...................3/17/10
A1737..................SUBMITTED................. 2/25/10
A1737..................Received....................2/17/10
A1737...................CLOSED....................4/12/10
So it should look like this
LoanApp_ID: .........PA-APPLREC Date ......DC:RECDATE
A1737..................... 1/12/10..................2/17/10
The Existing Query
SELECT
mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber,
CONVERT(varchar(10), MIN(mwlApprovalStatus.StatusDateTime),101) AS StatusDateTime,
MIN(mwlLoanApp.CurrentStatus) AS CurrentStatus,
MIN(mwlLoanApp.DecisionStatus) AS DecisionStatus,
CONVERT(varchar(10), MIN(mwlAS.StatusDateTime),101) AS [PA-APPLREC Date],
--DATEDIFF(day, MIN(mwlApprovalStatus.StatusDateTime), MIN(mwlAS.StatusDateTime)) AS [Diff],
MIN(mwlAS.StatusMWCode) AS StatusMWCode,
MIN(mwlLoanData.NoteRate) AS NoteRate,
MIN(mwlLoanData.BaseNoteAmt) AS BaseNoteAmt,
MIN(mwlLoanApp.OriginatorName) AS OriginatorName,
MIN(mwlLoanApp.LockDate) AS LockDate
FROM mwlLoanData AS mwlLoanData INNER JOIN
mwlLoanApp AS mwlLoanApp ON mwlLoanData.ObjOwner_ID = mwlLoanApp.ID INNER JOIN
mwlApprovalStatus AS mwlApprovalStatus ON mwlApprovalStatus.LoanApp_ID = mwlLoanApp.ID LEFT JOIN
mwlApprovalStatus AS mwlAS ON mwlApprovalStatus.LoanApp_ID = mwlAS.LoanApp_ID
AND mwlAS.StatusMWCode = 'PA-APPLREC'
GROUP BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber
ORDER BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

Does this give you what you want?

DECLARE @test TABLE (LoanApp_ID varchar(10), StatusMWCode varchar(20), StatusDateTime datetime);

INSERT INTO @test

SELECT 'A1737','APPROVED','3/5/10' UNION ALL

SELECT 'A1737','SCHEDULED','3/12/10' UNION ALL

SELECT 'A1737','PA-APPRV','1/20/10' UNION ALL

SELECT 'A1737','PA-APPLREC','1/12/10' UNION ALL

SELECT 'A1737','SUBMITTED','3/5/10' UNION ALL

SELECT 'A1737','FUNDING','3/17/10' UNION ALL

SELECT 'A1737','SUBMITTED','2/25/10' UNION ALL

SELECT 'A1737','Received','2/17/10' UNION ALL

SELECT 'A1737','CLOSED','4/12/10';

SELECT LoanApp_ID,

col2 = MAX(CASE WHEN StatusMWCode = 'PA-APPLREC' THEN StatusDateTime ELSE NULL END),

col3 = MAX(CASE WHEN StatusMWCode = 'Received' THEN StatusDateTime ELSE NULL END)

FROM @test t1

GROUP BY LoanApp_ID;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

davidskreczko Grasshopper Points: 22 More actions · Answer 2

Thanks for the help.

results are

LoanAPP-IP---------- 2COL ----------------Col3

A1737----------------2010-01-12--------2010-02-17

I don't know how I would use this in my query?