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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

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

Author

Topic

skreczko
Starting Member

6 Posts

Posted - 2010-10-14 : 16:04:56

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 it to give me the date with the RECEIVED Date. And call it DC:RecDate.
Right now it’s only pulling the earliest date per LoanApp_ID

Also, What does the 101 mean in this existing Query?

Thanks for your help

LoanApp_ID, StatusMWCode, StatusDesc, StatusDateTime
A1737, APPROVED, APPROVED, 3/5/10
A1737, SCHEDULED, Scheduled, 3/12/10
A1737, PA-APPRV, Preapproval approved, 1/20/10
A1737, PA-APPLREC, Preapproval received, 1/12/10
A1737, SUBMITTED, SUBMITTED, 3/5/10
A1737, FUNDING , Funding, 3/17/10
A1737, SUBMITTED, SUBMITTED, 2/25/10
A1737, Received, Received, 2/17/10
A1737, CLOSED, Closed 3/12/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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-14 : 17:51:07

the 101 means to display the date in mm/dd/yyyy format. see this: http://msdn.microsoft.com/en-us/library/ms187928.aspx

As for adding the received date...would need to see your table structure, but seems likely you want this


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,
	RECEIVED as [DC:RecDate]

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, RECEIVED
ORDER BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber

skreczko
Starting Member

6 Posts

Posted - 2010-10-15 : 09:41:46

Thanks Russell,

It didn't work. I get this messages
Msg 207, Level 16, State 1, Line 20
Invalid column name 'RECEIVED'.
Msg 207, Level 16, State 1, Line 12
Invalid column name 'RECEIVED'.

SEE DATA SAMPLE BELOW.
Im looking for all the dates(StatusDateTime)
with "StatusMWCode" =Received and call it DC:RECDATE.
And one that has a StatusMWCode of PA-APPLREC, which is already in the Query.

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

Thanks for the help

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-15 : 15:07:43

[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],
--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,
x.[DC:RecDate]

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'

INNER JOIN (
SELECT LoanApp_ID, StatusDateTime as [DC:RecDate]
FROM mwlApprovalStatus
WHERE StatusMWCode = 'Received'
) x
On x.LoanApp_ID = mwlApprovalStatus.LoanApp_ID

GROUP BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber, x.[DC:RecDate]
ORDER BY mwlLoanData.LoanProgramName, mwlLoanApp.LoanNumber
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources