Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2010
    Posts
    1

    Unanswered: I inherited this report , itís a way above my head

    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 from mwl.ApprovalStatus
    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If your previous code worked, then this one should work too:
    Code:
    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],
        CONVERT(varchar(10), MIN(mwlASReceived.StatusDateTime),101) AS [DC:RecDate],
        --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 OUTER JOIN mwlApprovalStatus AS mwlAS ON 
            mwlApprovalStatus.LoanApp_ID = mwlAS.LoanApp_ID AND 
            mwlAS.StatusMWCode = 'PA-APPLREC'
        LEFT OUTER JOIN mwlApprovalStatus AS mwlASReceived ON 
            mwlApprovalStatus.LoanApp_ID = mwlAS.LoanApp_ID AND 
            mwlAS.StatusMWCode = 'Received'
    GROUP BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber
    ORDER BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber
    Last edited by Wim; 10-17-10 at 15:05.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •