need help Guys.,
how can i convert money to varchar, to add some prefix. having problem with the below statement.
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],
The Complete Sproc is
USE FastLoan_Commissions
GO
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[upExceptions_GetPending]<br />
@UserID varchar(10)<br />
AS<br />
BEGIN<br />
<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END[Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NULL<br />
<br />
UNION ALL<br />
<br />
--UPFRONT LOAN INCREASES<br />
SELECT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
NULL [ExceptionDays],<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID IN (1,2)<br />
AND E.LoanIncreaseID IS NOT NULL<br />
<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOANS<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
Case When Isnull(LP.RefinanceAmount, '')='' Then L.LoanAmount Else Convert(money, 'N.F '+ LP.RefinanceAmount) END [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanPurpose LP ON LP.LoanID = E.LoanID AND LP.RefinanceReasonID IN (8,9,12)<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanStage LS12 ON LS12.LoanID = L.LoanID<br />
AND LS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
UNION ALL<br />
<br />
--TRAIL LOAN INCREASES<br />
SELECT DISTINCT E.ExceptionID,<br />
I.IntroducerID,<br />
I.TradingName,<br />
E.LoanID,<br />
'Loan Increase' [LoanIncrease], --tooltip<br />
A.ShortName,<br />
LIS12.StageDate [SettlementDate],<br />
CRT.CommissionRunType,<br />
CR.CommissionRunDate,<br />
ET.ExceptionType [Reason],<br />
LI.IncreaseAmount [Amount],<br />
ISNULL(L.CurrentBalance, 0) [CurrentBalance],<br />
E.ExceptionRate,<br />
E.ExceptionDays,<br />
E.ExceptionAmount,<br />
NULLIF(E.ExceptionGST, 0) [ExceptionGST],<br />
E.ExceptionTotal<br />
FROM tblCommissionRun CR<br />
INNER JOIN tblException E ON E.CommissionRunID = CR.CommissionRunID<br />
INNER JOIN FastLoan_Applications.dbo.tblIntroducer I ON I.IntroducerID = E.IntroducerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoan L ON L.LoanID = E.LoanID<br />
INNER JOIN FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.trelCustomerApplication CA ON CA.ApplicationID = L.ApplicationID<br />
LEFT JOIN FastLoan_Applications.dbo.tblCustomer C ON C.CustomerID = CA.CustomerID<br />
INNER JOIN FastLoan_Applications.dbo.tblLoanIncrease LI ON LI.LoanID = E.LoanID<br />
LEFT JOIN FastLoan_Applications.dbo.tblLoanIncreaseStage LIS12 ON LIS12.LoanIncreaseID = LI.LoanIncreaseID<br />
AND LIS12.LoanStageTypeID = 6<br />
LEFT JOIN trefCommissionRunType CRT ON CRT.CommissionRunTypeID = CR.CommissionRunTypeID<br />
LEFT JOIN trefExceptionType ET ON ET.ExceptionTypeID = E.ExceptionTypeID<br />
WHERE CR.Pending = 1<br />
AND CR.CommissionRunTypeID = 3<br />
AND E.LoanIncreaseID IS NOT NULL<br />
AND CA.ApplicantTypeID = 1<br />
<br />
ORDER BY TradingName, SettlementDate<br />
--CRT.CommissionRunType, CR.CommissionRunDate, SettlementDate<br />
END
Thanks in Advance
|