I have a current stored procedure.
Code:
SELECT
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 0 THEN
CASE WHEN LBAL.note_date < LBAL.imported_date THEN 0
ELSE
CASE WHEN LBAL.current_balance > 0 THEN
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 1 THEN
((LH.amount_financed * (LH.Dealer_Fee/100)) + LH.Non_Filing_Fees +
(LH.amount_financed * (LH.Manufacturer_Fee/100)))/(datediff(month, LBAL.date_contract_purchased, LBAL.note_date))
ELSE
((LH.amount_financed * (LH.Dealer_Fee/100 )) + LH.Non_Filing_Fees +
(LH.amount_financed * (LH.Manufacturer_Fee/100))) / (LBAL.number_of_payments)
END
END
END
ELSE
CASE WHEN LBAL.current_balance > 0 THEN
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 1 THEN
((LH.amount_financed * (LH.Dealer_Fee/100)) + LH.Non_Filing_Fees +
(LH.amount_financed * (LH.Manufacturer_Fee/100)))/(datediff(month, LBAL.date_contract_purchased, LBAL.note_date))
ELSE
((LH.amount_financed * (LH.Dealer_Fee/100 )) + LH.Non_Filing_Fees +
(LH.amount_financed * (LH.Manufacturer_Fee/100))) / (LBAL.number_of_payments)
END
END
END
AS NetIncomeAccretion,
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 0 THEN
CASE WHEN LBAL.note_date < LBAL.imported_date THEN 0
ELSE
CASE WHEN LBAL.current_balance > 0 THEN
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 1 THEN
Case When non_filing_fees > 50 then
0 - ((7.17 + (non_filing_fees - 50))/ (datediff(month, LBAL.date_contract_purchased, LBAL.note_date)) )
ELSE
0 - (7.17/ (datediff(month, LBAL.date_contract_purchased, LBAL.note_date)) )
END
END
ELSE
0 - ( 7.17/ (LBAL.number_of_payments) )
END
END
ELSE
CASE WHEN LBAL.current_balance > 0 THEN
CASE WHEN datediff(month, LBAL.date_contract_purchased, LBAL.note_date) > 1 THEN
Case When non_filing_fees > 50 then
0 - ((7.17 + (non_filing_fees - 50))/ (datediff(month, LBAL.date_contract_purchased, LBAL.note_date)) )
ELSE
0 - (7.17/ (datediff(month, LBAL.date_contract_purchased, LBAL.note_date)) )
END
ELSE
0 - (7.17/ (LBAL.number_of_payments) )
END
END
END
AS NetCostAccretion,
(LH.amount_financed * (LH.Dealer_Fee/100)) + LH.Non_Filing_Fees +
(LH.amount_financed * (LH.Manufacturer_Fee/100)) as Total_Fees
FROM
monthly_loan_ledger_balance LBAL
join Avalanche..Loan_Header LH
on LBAL.Loan_Number = LH.Loan_Number
where ((day(LBAL.imported_date) > 25
AND month(LBAL.imported_date) = '04'
AND year(LBAL.imported_date) = '2007')
OR (day(LBAL.imported_date) < 10
AND month(LBAL.imported_date) = '04' + 01 -- requested month + 1
AND year(LBAL.imported_date) = '2007'))
AND datediff(month, getdate(), LBAL.maturity_date) > 1
AND LBAL.current_balance > 0
AND LBAL.loan_number >= 189625
AND LH.company_number = 03
order by LBAL.current_balance
It pulls data from the current month based on the criteria. I want to add some columns that will allow me to compare data from last month, and months back. How can I add that. I realize that the same account could be pulled from the previous month, so that may factor in the thought process too.
Looking for an output like this:
Loan Number
Maturity Date
Note Date
Current Balance
Company Number
Imported Date
Deferred Term
MarchNetIncomeAccretion **New Column
MarchNetCostAccretion **New Column
NetIncomeAccretion
NetCostAccretion
So you want to create new columns for the prior months or...? Do you know how far back you need to go?
You would have to start with a start and end date. Then you determine how many months are between them. Once you have done that you can decide where to go from there. Do you have any sample result sets I can look at so I can get a better picture <confession> I did not read all the code; as I find it difficult to conceptualize from code alone. </confession>.
Also would you only be looking at *one* loan at a time this way, otherwise it might be too challenging (not that it is not already).
Was this answer helpful ?
Yes No
Quote:
| Originally Posted by Lauramc So you want to create new columns for the prior months or...? Do you know how far back you need to go?
You would have to start with a start and end date. Then you determine how many months are between them. Once you have done that you can decide where to go from there. Do you have any sample result sets I can look at so I can get a better picture <confession> I did not read all the code; as I find it difficult to conceptualize from code alone. </confession>.
Also would you only be looking at *one* loan at a time this way, otherwise it might be too challenging (not that it is not already). |
Ok, you would be only looking at one loan at a time. The challenging part perhaps would be that the same loan could appear months back, especially if there isn't a zero balance. So perhaps a UNION statement may work. Not real sure but I have seen it work before where I had to put two date ranges in and pulled the same data based on the date ranges. If they were alike, I had data put in, else there was a zero for that date range field.
Here is some sample data that I am pulling from the two tables.
Was this answer helpful ?
Yes No
Here is a sample output from the stored proc.
Was this answer helpful ?
Yes No
From context here, I am guessing that you have a loan ID that is unique to the loan, but that there is a listing for each loan for each month. So I guess the next question is - what are you looking for? Are you trying to get a history of a particular loan so you can see (for example) what the loan balance was in the past? Would you also be passing in a loan ID as part of your procedure?
I can see what your current procedure outputs, but I am wondering what you want the data to look like once your procedure is ideal. Adding columns might work, but only if you just need say - one month prior. If you get a whole year's worth of months that would not be feasible.
A union may work, but only if you don't mind having multiple rows (one for each month) per loan. Also you would have to make sure that the loan is at least one month old before attempting to get the data.
If you have an idea of your ideal result set, I can try and work backwards from that
Was this answer helpful ?
Yes No
If I want to compare the current month (April data) to March Data, I use this and it works:
Code:
select
C.loan_number,
C.maturity_date,
C.note_date,
C.current_balance,
C.company_number,
C.imported_date,
C.DeferredTerm,
C.NetIncomeAccretion,
C.NetCostAccretion,
C.TotalFees,
M.NetIncomeAccretion as MarchNetIncomeAccretion,
M.NetCostAccretion as MarchNetCostAccretion,
M.TotalFees_M as MarchFees
from current_fasb C left join march_fasb M on C.loan_number = M.loan_number
I now want to add Feb and Jan to the query (3 rows from each table)
Code:
select
C.loan_number,
C.maturity_date,
C.note_date,
C.current_balance,
C.company_number,
C.imported_date,
C.DeferredTerm,
C.NetIncomeAccretion,
C.NetCostAccretion,
C.TotalFees,
M.NetIncomeAccretion as MarchNetIncomeAccretion,
M.NetCostAccretion as MarchNetCostAccretion,
M.TotalFees_M as MarchFees,
F.NetIncomeAccretion as FebruaryNetIncomeAccretion,
F.NetCostAccretion as FebruaryNetCostAccretion,
F.TotalFees_M as FebruaryFees,
J.NetIncomeAccretion as JanuaryNetIncomeAccretion,
J.NetCostAccretion as JanuaryNetCostAccretion,
J.TotalFees_M as JanuaryFees
from current_fasb C left join march_fasb M on C.loan_number = M.loan_number
left join feb_fasb F on C.loan_number = F.loan_number
left join jan_fasb J on C.loan_number = J.loan_number
My question is on the join. I pulled over 75K records with just the two tables. I would think I would pull even more records adding two additional tables. What am I not seeing or what am I doing wrong on the joins?
Was this answer helpful ?
Yes No
you would only get more rows if those tables had a many to one relationship with your first table.
Was this answer helpful ?
Yes No
Maybe not. Just more data in your columns. The reason for this is that you are basing ALL of this on the Current month's data. Therefore, the recordset is limited to only what is in the current month. So if something existed in March but not April, you would never see it.
Does that make sense? Is that what you want? If not, I suppose you could look into a full join (I have never used it).
Edit: LOL mehere - we need to stop doing that!!! We keep posting at the same time
Was this answer helpful ?
Yes No
Example
Loan in Jan
Same loan in Feb
Same loan in Mar but pays it off, so should show nothing in April (0)
That would be one record
Loan in April (one record)
Loan in March but pays it off (zero for Current)
So how can I use a full join if that is what you recommend?
I guess I don't understand why it is pulling less records. HEAD BANGING SYNDROME...
Was this answer helpful ?
Yes No
try using FULL OUTER JOIN instead of LEFT JOIN
Was this answer helpful ?
Yes No
