sql query nightmare part II

Check out below - have created a test table or two - so please take care with naming conventions...

-- step 1 create test loan_table
create table loan_table (code varchar(20),startdate datetime, apr decimal(9,5), term int)
insert loan_table values ('NV','24 Nov 1994',7.14,36)
GO
 
-- step 2 create test FHL_Rates table
create table FHL_Rates (Period datetime, [1] decimal(9,5),[2] decimal(9,5),[3] decimal(9,5),[4] decimal(9,5),[5] decimal(9,5),[6] decimal(9,5),[12] decimal(9,5),[18] decimal(9,5),[36] decimal(9,5),[48] decimal(9,5))
insert FHL_Rates values ('19 Nov 1994',6.13,6.24,6.47,6.79,6.9,6.97,7.01,7.11,7.14,7.15)
GO
 
-- step 4 is how we use the function - but need to do step 3 first...
select Code, StartDate, APR,Term, dbo.uFHLRate(startdate,term) as Official_Rate from loan_table
GO
 
-- step 3 create the function
create function uFHLRate(@startdate datetime, @term int)
returns decimal(9,5)
as
begin
return (select top 1 case 
					 when @term = 1 then [1]
					 when @term = 2 then [2]
					 when @term = 3 then [3]
					 when @term = 4 then [4]
					 when @term = 5 then [5]
					 when @term = 6 then [6]
					 when @term = 12 then [12]
					 when @term = 18 then [18]
					 when @term = 36 then [36]
					 when @term = 48 then [48]
                     else 0.0000
                     end
        from FHL_Rates where period <= @startdate order by period desc) 
end
GO

Select allOpen in new window

So, why are we creating two new tables when these already exists?

Just for the purposes of the test - I wanted to make sure it works, and it give a reference point for the function and the select - now other reason. You should change the names according to your real names...

sorry, that should be NO other reason... just easier to test seperately before applying any great volumes.

Test seems to be working, you guys are smart.
Let me know test it against my real tables with lots of data....

Won't be the quickest - will need indexes on the FHL rates table on that date column...

now, i am calling following select stmt from a real table, and it throws this error

select code,term,opened,apr,dbo.uFHLRate(startdate,term) as Official_Rate from loan

Error
Msg 207, Level 16, State 3, Line 1
Invalid column name 'startdate'.

that was it, it is working great but dont know how, guess my knowldege in sql is minimal, thank you so much for the help...

A pleasure, feel free to ask any question about "how" and I will try to explain...

e.g. a function is a construct in SQL server that can be included in a query. It is very similar to a stored procedure with a few limitations (like it cannot call procedures). But it does allow you to return values dynamically from passing a few parameters to it. So, in situations where there is pretty much a row by row requirement (such as a rate lookup, currecny conversion, formatting etc), then it is likely that a user defined function is required. In that regard, it is similar to other SQL functions like "convert".

Nice explanation, I was testing and just found out that official rate is month behind, for example i ran this query

select acct,code,sub,term,opened,apr,dbo.uFHLRate(opened,term) as FTP_Rate,loan.status from loan,memb
where opened between '11/1/1995' and '12/31/1996'
and memb.rowno = rowno_membloan_memb
and term in ('1','2','3','4','5','6','12','18','24','36','48','60','72','84','96','108','120','180','240')
and loan.status = 'active'

here are first two rows

acct   code sub             term              opened                 apr                 Official_Rate      Status
3219  TS            1             1                1996-10-10          12.90             5.53000                active
1422  HE            1           180             1996-08-15           5.50              7.48000                 active

Then I ckecked my FHL Rate table and there


Date                         Rate
September-96       5.53
October-96             5.44

on the first acct open date is in October 1996 but it shows official rate of September 96

on the second account same thing, loan was opened on August of 1996, but it pulled July 1996 official rate

FHL Table

July-96             7.48
August-96        7.34


you think something wrong with the funtions.....

Thanks

Well, it is checking "raw" datetimes for the most recent FHL rate which is less than or equal to the actual date...

so if FHL date is say 18th of each month then the previous month would be picked up in both circumstances.

Would need to see : select * from fhl_rate_table where month(period) in (9,10) and year(period) = 1996   -- you need to change the column name and table name accordingly.

Mark, Thank you
FHL rates come to us once a month on diff dates, I have attached the jpeg file after runnig what you asked...







FHL.jpg

OK, then the function is performing what was expected. If the day part of the date is irrelevant, then use the following...

again, you will need to change the column named "period" to whatever the real one is...

and, if there will always be a "period" for each and every month, then could simplify (and speed up a bit more) by replace the "from" line with :

from FHL_Rates where convert(varchar(6),period,112) = convert(varchar(6),@startdate,112) )


the convert is basically putting it in YYYYMM format - you can see it by doing : select convert(varchar(6),getdate(),112)

alter function uFHLRate(@startdate datetime, @term int)
returns decimal(9,5)
as
begin
return (select top 1 case 
					 when @term = 1 then [1]
					 when @term = 2 then [2]
					 when @term = 3 then [3]
					 when @term = 4 then [4]
					 when @term = 5 then [5]
					 when @term = 6 then [6]
					 when @term = 12 then [12]
					 when @term = 18 then [18]
					 when @term = 36 then [36]
					 when @term = 48 then [48]
                     else 0.0000
                     end
        from FHL_Rates where convert(varchar(6),period,112) <= convert(varchar(6),@startdate,112) order by period desc) 
end
GO

Select allOpen in new window

Awesome, you are the MAN.
So, lets see if i got this,
basically you converted the date field to year and month and skipped the day, and now things are working the way it suppose to, correct.
Beautiful....

yep, that's it... and it is good night from me (5:30am here and need some sleep before the sun comes up)...