August 10, 2009 at 12:24 pm
I get a 'collation conflict between "Latin_General_CI_AS_KS_WS" and "SQL_Latin1_General_CP1_CI_AS": in the equal to operation error.
When I run this :
DECLARE @DATE AS DATETIME
DECLARE @DATA_DATE AS DATETIME
DECLARE @PEOM_DATE AS DATETIME
SET @DATE = '03/04/2008'
SET @DATA_DATE = --@DATE
(
SELECT DISTINCT
(CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT
WHEN IS_BUSINESS_DAY = 1 THEN DATE
WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT
END)
FROM
LPB_REPORTINGSERVICES.DBO.DIM_DATE
WHERE
DAY_IN_MONTH = 1
AND MONTH(DATE) = MONTH(@DATE)
AND YEAR(DATE) = YEAR(@DATE)
)
SET @PEOM_DATE = DATEADD(M,-1,@DATA_DATE)
SET @PEOM_DATE =
(
SELECT DISTINCT
(CASEWHEN IS_HOLIDAY = 1 THEN BUSINESS_DAY_NEXT
WHEN IS_BUSINESS_DAY = 1 THEN DATE
WHEN IS_WEEKDAY = 0 THEN BUSINESS_DAY_NEXT
END)
FROM
LPB_REPORTINGSERVICES.DBO.DIM_DATE
WHERE
DAY_IN_MONTH = 1
AND MONTH(DATE) = MONTH(@PEOM_DATE)
AND YEAR(DATE) = YEAR(@PEOM_DATE)
)
--SELECT @DATE, @DATA_DATE,DATEADD(M,-1,@DATA_DATE),@PEOM_DATE
------------------------------------------------------
create table ##Cash_Flow1
(
loannumbernvarchar(10),
PmtTransDatedatetime,
pmtprinamtmoney,
pmtsuspenseamtmoney,
pmtintamtmoney,
pmtescrowamtmoney,
PmtFeeAmountmoney,
pmtprinamt_1money,
PmtTotalAmtmoney
)
insert into ##Cash_Flow1
select distinct
loannumber,
pmttransdate,
sum((casewhen pmtprinamt < 0 then 0 else pmtprinamt end)) as 'Principal Pmt',
sum(pmtsuspenseamt) as 'Pmt Suspense',
sum(pmtintamt) as 'Interest Pmt',
sum(pmtescrowamt) as 'Escrow Pmt',
sum(PmtFeeAmount) as 'Pmt Fee Amount',
sum(pmtprinamt) as 'PPA 1',
sum(PmtTotalAmt) as 'Pmt Total Amount'
from
lpb_loanservicing.dbo.PaymentTransaction pt
where
pmttranscode in (171,172,173,174,175,181,182)
and
pmttransdate = (select max(pmttransdate) from lpb_loanservicing.dbo.PaymentTransaction where loannumber = pt.loannumber and pmttransdate <= @DATA_DATE and pmttranscode in (171,172,173,174,175,181,182))
group by
loannumber,
pmttransdate
create table ##Final_CashFlow1
(
loan nvarchar(10),
PmtTransDatedatetime,
pmtprinamtmoney,
pmtintamtmoney,
pmtescrowamtmoney,
PmtFeeAmountmoney,
pmtprinamt_1money,
PmtTotalAmtmoney,
pmtsuspenseamtmoney,
Cal_TotalPmtAmt money,
Less_Than_MPvarchar(10),
Greater_Than_30 varchar(10)
)
insert into ##Final_CashFlow1
select distinct
l.loan,
PmtTransDate,
pmtprinamt,
pmtintamt,
pmtescrowamt,
PmtFeeAmount,
pmtprinamt_1,
PmtTotalAmt,
pmtsuspenseamt,
isnull(pmtprinamt,0) + isnull(pmtintamt,0) +
isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0
else pmtprinamt_1
end),0),
(casewhen
isnull(pmtprinamt,0) + isnull(pmtintamt,0) +
isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0
else pmtprinamt_1
end),0) < (ISNULL(TotalMnthlyPmt,0) - ISNULL(TAndIMnthlyAmt,0)) then 'Review'
else ''
end) as 'Less Than Min Payment',
(casewhen
isnull(pmtprinamt,0) + isnull(pmtintamt,0) +
isnull((casewhen isnull(pmtprinamt_1,0) > 0 then 0
else pmtprinamt_1
end),0) > FULLAMORTIZEDPIAMT and FULLAMORTIZEDPIAMT <> 0 then 'Review'
else ''
end) as 'Greater than 30'
from
lpb_loanservicing.dbo.loandata l
inner join ##Cash_Flow1 cf
on l.loan = cf.loannumber
where
zone not in ('W9','WI','W8')
and
investor not in ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')
AND
isnull(payoffeffdate,'1900-01-01 00:00:00.000') = '1900-01-01 00:00:00.000'
AND
CONVERT(CHAR(10),DATA_DATE,101) = CONVERT(CHAR(10),@DATA_DATE,101)
and
principalbalance > 0
and
category not in ('012')
DECLARE @BALANCES TABLE
(
LOAN VARCHAR(10),
PRINCIPALBALANCE FLOAT
)
INSERT INTO @BALANCES
SELECT DISTINCT
LOAN,
PRINCIPALBALANCE
FROM
LPB_LOANSERVICING.DBO.LOANDATA
WHERE
CONVERT(CHAR(10),DATA_DATE,101) = @PEOM_DATE --CONVERT(CHAR(10),DATEADD(M,-1,@DATA_DATE),101)
AND CATEGORY IN ('001','011','005','014','015','016','017')
AND ZONE NOT IN ('WI','W9')
ANDINVESTOR NOT IN ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')
SELECT DISTINCT
@DATA_DATE AS 'DATA DATE',
L.LOAN,
L.PRINCIPALBALANCE 'Current EOM BALANCE',
B.PRINCIPALBALANCE as 'Previous EOM BALANCE',
OTHERFUNDSDUE + ACCRLCAMOUNT AS 'FEES',
ACCRUEDINTEREST,
ISNULL(TotalMnthlyPmt,0) - ISNULL(TAndIMnthlyAmt,0) AS 'Minimum Payment',
LOANTERM,
NEXTDUEDATE as 'Next Due Date',
lastfullpmtdate as 'Last Full PMT Date',
PmtTransDate as 'Pmt Trans Date',
pmtprinamt as 'Pmt Prin Amt',
pmtintamt as 'Pmt Int Amt',
pmtescrowamt as 'Pmt Escrow Amt',
PmtFeeAmount as 'Pmt Fee Amt',
pmtprinamt_1 as 'Pmt Prin Amt 1',
PmtTotalAmt as 'Pmt Total Amt',
pmtsuspenseamt as 'Pmt Suspsense Amt',
Cal_TotalPmtAmt as 'Cal Total Pmt Amt',
(CASE
WHEN INVESTOR = 'T61' AND CATEGORY = '001' THEN 'HELOC PURCHASED'
WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '601'THEN 'HELOC PURCHASED'
WHEN INVESTOR = 'T61' AND CATEGORY = '002' THEN 'CHOICE'
WHEN INVESTOR = 'T61' AND CATEGORY = '003' THEN 'TRADITIONAL'
WHEN INVESTOR = 'T61' AND CATEGORY = '004' THEN 'FIXED'
WHEN INVESTOR = 'T61' AND CATEGORY = '005' THEN 'FIXED RATE SECONDS'
WHEN INVESTOR = 'T61' AND CATEGORY = '006' THEN 'TRADITIONAL'
WHEN INVESTOR = 'T61' AND CATEGORY = '007' THEN 'FIXED'
WHEN INVESTOR = 'T61' AND CATEGORY = '008' THEN 'TRADITIONAL'
WHEN INVESTOR = 'T61' AND CATEGORY = '009' AND BRANCHOFFICECODE IN ('27','28','29','39','44') THEN 'BAC'
WHEN INVESTOR = 'T61' AND CATEGORY = '009' THEN 'TRADITIONAL'
WHEN INVESTOR = 'T61' AND CATEGORY = '010' THEN 'FIXED'
WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('01','001','0001','1') THEN 'HELOC RETAIL'
WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '600' THEN 'HELOC RETAIL'
WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('02','002','0002','14','2') THEN
(CASEWHEN PROPERTYSTATE = 'CA' THEN 'CALIFORNIA - WHOLESALE HELOC'
WHEN PROPERTYSTATE = 'FL' THEN 'FLORIDA - WHOLESALE HELOC'
WHEN PROPERTYSTATE = 'AZ' THEN 'ARIZONA - WHOLESALE HELOC'
ELSE 'ALL OTHER STATES - WHOLESALE HELOC'
END)
WHEN INVESTOR = 'T61' AND CATEGORY = '011' AND BRANCHOFFICECODE IN ('0120') AND PRODUCTLINECODE = '603' THEN
(CASEWHEN PROPERTYSTATE = 'CA' THEN 'CALIFORNIA - WHOLESALE HELOC'
WHEN PROPERTYSTATE = 'FL' THEN 'FLORIDA - WHOLESALE HELOC'
WHEN PROPERTYSTATE = 'AZ' THEN 'ARIZONA - WHOLESALE HELOC'
ELSE 'ALL OTHER STATES - WHOLESALE HELOC'
END)
WHEN INVESTOR = 'T61' AND CATEGORY = '014' THEN 'HELOC FIRST'
WHEN INVESTOR = 'T61' AND CATEGORY = '015' THEN 'HELOC FIRST'
WHEN INVESTOR = 'T61' AND CATEGORY = '016' THEN 'HELOC FIRST'
WHEN INVESTOR = 'T61' AND CATEGORY = '017' THEN 'HELOC FIRST'
WHEN INVESTOR = 'V61' AND CATEGORY = '002' THEN 'CHOICE OTHER'
WHEN INVESTOR = 'V61' AND CATEGORY = '006' THEN 'TRADITIONAL'
WHEN INVESTOR = 'W61' AND CATEGORY = '002' THEN 'CHOICE OTHER'
WHEN INVESTOR = 'W61' AND CATEGORY = '006' THEN 'TRADITIONAL'
WHEN INVESTOR = 'X61' AND CATEGORY = '006' THEN 'TRADITIONAL'
WHEN INVESTOR = 'X61' AND CATEGORY = '008' THEN 'TRADITIONAL'
WHEN INVESTOR = 'Y60' AND CATEGORY = '001' THEN 'AUTO'
WHEN INVESTOR = 'Z61' AND CATEGORY = '002' THEN 'CHOICE OTHER'
WHEN INVESTOR = 'Z61' AND CATEGORY = '003' THEN 'TRADITIONAL'
WHEN INVESTOR = 'Z61' AND CATEGORY = '004' THEN 'FIXED'
WHEN INVESTOR = 'Z61' AND CATEGORY = '005' THEN 'FIXED RATE SECONDS'
WHEN INVESTOR = 'Z61' AND CATEGORY = '006' THEN 'TRADITIONAL'
WHEN INVESTOR = 'Z61' AND CATEGORY = '007' THEN 'FIXED'
WHEN INVESTOR = 'Z61' AND CATEGORY = '008' THEN 'TRADITIONAL'
WHEN INVESTOR = 'Z61' AND CATEGORY = '009' THEN 'TRADITIONAL'
WHEN INVESTOR = 'Z61' AND CATEGORY = '010' THEN 'FIXED'
WHEN INVESTOR = 'Z61' AND CATEGORY = '011' THEN 'HELOC SECOND'
WHEN INVESTOR = 'Z61' AND CATEGORY = '014' THEN 'HELOC FIRST'
WHEN INVESTOR = 'Z61' AND CATEGORY = '015' THEN 'HELOC FIRST'
WHEN INVESTOR = 'Z61' AND CATEGORY = '016' THEN 'HELOC FIRST'
WHEN INVESTOR = 'Z61' AND CATEGORY = '017' THEN 'HELOC FIRST'
END) AS 'GROUPING ACCOUNTING',
discountamount,
firstsrvfeerate,
firstunamortizedfeeamt,
firstfasbfee1,
firstfasbfee2,
firstfasbfee3,
firstfasbfee4,
ANNUALINTRATE AS 'ANNUALINTRATE',
(casewhen (isnull(L.PRINCIPALBALANCE,0) - (isnull(B.PRINCIPALBALANCE,0) - isnull(pmtprinamt,0))) > 0 then (isnull(L.PRINCIPALBALANCE,0) - (isnull(B.PRINCIPALBALANCE,0) - isnull(pmtprinamt,0))) end) as 'Draws',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) <= 4 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'UP TO DATE BALANCE',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 5 AND 29 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 1 Balances' ,
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 30 AND 59 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 2 Balances',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 60 AND 89 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 3 Balances',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 90 AND 119 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 4 Balances',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 120 AND 149 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 5 Balances',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) BETWEEN 150 AND 179 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Cycle 6 Balances',
(CASEWHEN PAYOFFEFFDATE = '01/01/1900' THEN
(CASEWHEN DATEDIFF(DD,NEXTDUEDATE,@DATA_DATE) >= 180 THEN L.PRINCIPALBALANCE
else 0 END)
else 0
END) 'Chargeoff balances',
(CASEWHEN DATEDIFF(DD,@DATA_DATE,NEXTDUEDATE) > 0 THEN 0
ELSE DATEDIFF(DD,@DATA_DATE,NEXTDUEDATE)
END) AS 'DAYS PAST DUE',
(CASEWHEN PAYOFFEFFDATE <> '01/01/1900' THEN PAYOFFEFFDATE END) PAYOFFEFFDATE
FROM
LPB_LOANSERVICING.DBO.LOANDATA L
LEFT JOIN @BALANCES B
ON L.LOAN = B.LOAN
LEFT JOIN ##Final_CashFlow1 FC
ON L.LOAN = FC.LOAN
WHERE
convert(char(10),DATA_DATE,101) = @DATA_DATE
AND CATEGORY IN ('001','011','005','014','015','016','017')
AND ZONE NOT IN ('WI','W9')
ANDINVESTOR NOT IN ('J61', 'L60', 'S61', 'D81', 'R81', 'U81', 'W81','K61','W35','V81','R61')
ORDER BY 1
drop table ##Cash_Flow1
drop table ##Final_CashFlow1
August 10, 2009 at 12:29 pm
Without the DDL (including the collation for character fields) a little hard to determine where the problem is with the code.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply