Changing User defied Function

[drek01]

I HAVE THREE TABLES TBLEXPAFTERutil, TBLEXPECTEDLOSS, and tblfacility

recoveryrate. the values on the fields of those tables changes after certain

time(frequently, 6-12 months). so how my system is now is that the field is changed

on table and older field is overwritten with new field. so when the report is run,

it displays the values with current records from that field.
so after the fields are changed(overwritten) if i run the reports from previous year it still takes the values from current time, which outputs the reports with wrong results.

so i want to implement the way after the values in the fields are changed, the new report would take the values from new field while old report would take values from time.
here are the field in that tables which changes after certain period.
tblExpAfterutil- "RiskGrade"," DrawRatio"
tblExpectedLoss-"RiskGrade","DrawRatio","JapanesePd","NonJapanesePd"
tblFacilityrecoveryrates- 'facilityRating','RecoveryRates'
* DrawRation and RiskGrade in both tables will hold same records.

so how sould i do that, should i create a new table after everychages and change in

store procedure and and function if the given date is certain, then use values from this table, if not from other table.then looks like after every 6-12 months or so i
have to create new tables. or should i add one more field in table like have datefield, and after changes are added( withoutover writing previous rates and ratio....) put certain date and when reports of previous years are run take the values from that field for calculation. But still i donot know how to implement this.
i have attached the all storeprocedure,function,and tables, and code for form from where report is opened.

help me out here. Guys
Appreciate it
Derek

here is one of the function that is used called in my store procedure. Please tell me how to modify the function so that when exam date( report for that year) can be passed as parameter and according to that year, it would select the rating index, rates and other values, for calculation of that year. i am thinking about adding date field in all those table(year) for different ratings and ration.

ALTER  Function Fn_CalcExpAfterUtil
(@Mat smalldatetime , @InitialDate smalldatetime, @AuthorizeDate smalldatetime,
@TradeID varchar(50), @RiskGrade varchar(50), @ExpComm bigint, @ExpTF bigint, @Exp bigint)
Returns float
as
Begin
	Declare @RetVal float
	Declare @RatingIndex Bigint
	Declare @Draw AS Float

	If (@ExpComm = 0 And @ExpTF = 0)
		Begin
		    Set @RetVal=@Exp
		    Goto ExitRoutine
		End

	If (@ExpTF > 0)
	Begin
	    If (@TradeID <> 'LCI' And @TradeID <> 'LCL' And @TradeID <> 'LCPRC' And @TradeID <> 'LCU')
	   	    Begin
			    Set @RetVal=@Exp
			    Goto ExitRoutine
			End
	End

	If @RiskGrade = '99' Or @RiskGrade = '00' Or Len(Isnull(@RiskGrade,0)) = 1
		Begin
		    If (Len(Isnull(@InitialDate,0)) <> 0 And Len(Isnull(@AuthorizeDate,0)) <> 0)
			    Begin
				    If (@AuthorizeDate < '7/10/01')
						Begin
			            	Set @RiskGrade = '06'
						End
			        Else If (@InitialDate < '7/10/01')
						Begin
				            Set @RiskGrade = '06'
						End
			        Else
						Begin
				            Set @RiskGrade = '061'
						End
		        End
		    Else
				Begin
			        Set @RiskGrade = '06'
			    End
		End

		Set @RatingIndex = 1

	If (Len(Isnull(@InitialDate,0)) <> 0 And Len(Isnull(@AuthorizeDate,0)) <> 0)
	Begin
	    If (@AuthorizeDate < '7/10/01')
			Begin
		        Set @RatingIndex = 1
			End
	    Else If (@InitialDate < '7/10/01')
			Begin
		        Set @RatingIndex = 1
			End
	    Else
			Begin
		        Set @RatingIndex = 2
		    End
	End


	If (@RatingIndex = 2)
		Begin
		    If (@RiskGrade = '05' Or @RiskGrade = '06')
				Begin
			        Set @RiskGrade = @RiskGrade + '1'
			    End
		End 

	If (@RiskGrade = '051' Or @RiskGrade = '052' Or @RiskGrade = '061' Or @RiskGrade = '062' Or @RiskGrade = '083')
		Begin
		    Set @RatingIndex = 2
		End


	If (@RatingIndex = 1)
		Begin
			Select @Draw=(DrawRatio/100) from tblAQRExpAfterUtil where RatingIndex=1 and RiskGrade=left(@RiskGrade, 2)
		End
	Else
		Begin
			Select @Draw=(DrawRatio/100) from tblAQRExpAfterUtil where RISKGRADE =@RISKGRADE AND RATINGINDEX <> 1
		End



	Set @RetVal= @Exp * @Draw
	Goto ExitRoutine

ExitRoutine:
	Return @RetVal

End

and here is the code from where the report is called to be dispalyed.

Private Sub cmbDateList_Enter()
Dim strSql As String
strSql = "SELECT DISTINCT([yymmdd]) FROM tblmaster where OrgUnit = '" & Me.cmbExamList & "' "
Me.cmbDateList.RowSource = strSql

End Sub

Private Sub cmbExamList_Enter()
Me.cmbDateList = ""
End Sub

Private Sub cmbRegionList_Enter()
    Me.cmbRegionList.Requery
End Sub
Private Sub cmdShowReport_Click()
Dim bflag As Boolean
On Error GoTo Error_cmdShowReport_Click

    bflag = bValidate("EXAM")
    If bflag = False Then
        GoTo Exit_cmdShowReport_Click
    End If

    bflag = bExecuteQuery("EXAM", "")
    If bflag = False Then
        GoTo Exit_cmdShowReport_Click
    End If
           
    If Me.cmbGradeList = "Original Grade" Then
        DoCmd.OpenReport "rptAssetRating_OrgGrade", acPreview
    ElseIf Me.cmbGradeList = "CEA Grade" Then
        DoCmd.OpenReport "rptAssetRating_CEAGrade", acPreview
    End If
    Me.Visible = False
    
Exit_cmdShowReport_Click:
    Exit Sub
Error_cmdShowReport_Click:
    MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
    Resume Exit_cmdShowReport_Click
End Sub

 

[Lamprey13]

Can you post DDL, Sample data and expected output?

I'm confused because you are saying that the fields are overwritten, so I don't understand how you can get the old value out.

 

[drek01]

till now the new (records on same )fields overwrites the old records. so when i have to run the reports from few years back it takes the new records from that table, so the reports supposed to be based on some values of that years is actually using the values for current year.

Now what i am trying to do is adding new field called 'date' and instead of overwritng adding new records with date ( year) on it. so after i change the record, if i want to run reports for previous year it should use that values for that years.

Hope you got some ideas.

 

[SQLBill]

I agree with Lamprey, you need to provide us the schema, some sample data as it would appear in the tables and what you are trying to do.

The problem is that if you are CHANGING data (UPDATE), then you only have one version of the data regardless of what the data is.

For example.......

FName LName Phone Date
Bill Smith 123-5678 2007-04-30 05:00

Now you want to change the phone number. So you UPDATE it and you have a trigger that updates the Date.:

FName LName Phone Date
Bill Smith 123-4567 2007-04-30 06:00

You still only have that one value in the table. The old value is gone, because you changed it.

Now, maybe you are going to just keep adding (INSERT) a new row every time a change is made. That's a different issue. Also, it will make for a HUGE database.

-SQLBill



Posting advice: FAQ481-4875

 

[drek01]

Rating RiskGrade Drawratio ID
Index

1 01 4.038 1
1 02 9.023 2
1 03 18.833 3
1 04 36.223 4
1 05 47.051 5
1 06 56.919 6
1 07 66.027 7
1 7W 66.027 8
1 08 81.772 9
1 09 100. 10
1 10 100. 11
0 01 5.43 12
0 02 10.62 13
0 021 10.62 14
0 022 10.62 15
0 023 10.62 16
0 03 16.28 17
0 031 15.45 18
0 032 16.28 19
0 033 18.6 20
0 04 30.49 21
0 051 39.77 22
0 052 42.44 23
0 061 51.74 24
0 062 56.59 25
0 07 70.0699 26
0 081 71.7 28
0 082 72.9000 29
0 083 100. 30

This is tblAQRExpAfterUtil
here the risk grade and drawratio changes every year. like for risk grade 01, drawing ration might me different next year and vice versa.


ID JapanesePD NonJapPD DrawRatio

01 0. 5.5e-002 5.43
02 2.e-002 0.1575 10.62 2
021 2.e-002 0.11125 10.62 3
022 2.e-002 0.1575 10.62 4
023 2.e-002 0.22125 10.62 5
03 8.e-002 0.38125 16.28 6
031 8.e-002 0.29625 15.45 7
032 8.e-002 0.38125 16.28 8
033 8.e-002 0.4475 18.6 9
04 0.13 0.6825 30.49 10
051 0.2 1.155 39.77 11
052 0.29 1.845 42.44 12
061 0.49 3.0175 51.74 13
062 0.64 6.485 56.59 14
07 1.15 7.295 70.069 15
081 4.75 14.3675 71.7 16
082 12.18 27.02 72.900 17
083 11.9 50. 100. 18


here as well japanesePD, NonJapanesePD and Drawratio changes every year.
and i still have one more table with same data types.

Hope ypu got the idea. Appreciate any help guys.

 

[SQLSister]

Anytime you have historical data concerning rates or prices, you should store that data as part of the orginal record (order, quote, whatever). Join to the rate table or price table to get the current price when creating a record, but always use the rate or price in the order or quote (or whatever thing you are storing) in any reports.

This is the structure you must always use if you are going to expect to have accurate historical reports over time.

Trying to calculate what the rate might have been based on how much we usually increment it will get you into trouble sooner or later as it may not be not the rate you actually charged. This is a serious flaw in your structure and creates accounting problems that could drive legal issues. You cannot afford to leave this structure the way it currently is. You must change your database to correctly reflect the amount charged or the rate used in the calculation at the time of the action. To do anything else is to risk a lawsuit.

Questions about posting. See faq183-874

 

[drek01]

Thanks for tips SQLSister. i know exactly what you mean. since this hasto be fixed asap. what you suggest to make it work, thats first thing.


this is the database from previous one( who was using this).
these are the the rates( drawing ratio) stored in database. according to those rates the values are calculated.

and they actually implement it after every year. so in order to even retrieve old reports using rates for that year how should i enhance database and modify my function.
since they have to modify the Drawing ratio every year,and actually till now they were overwrting those Drawing ratio for every new year. should i add date field for new modified and impplement the function and store procedure in same way.
help me out here. if i have to add date how should i add it that after every year of new ratio, i should be able to run that reports using value of that year.

as i have posted my fucntion on previous post as well.
any help will be appreciated.

 

[drek01]

lets say, the new aadded field in those tables,lets take 'tblexpectedutility' the new drawing ratio is added for different risk grade,( rating index for new would be 0), and i added new field exam date( where the change date for new drawing ration would be added).lets say i made changes of my drawing ratio on 1/1/2008'. since i have to run the report,
should i made just these changes on my function, which calculates the desired value to be displayed on reports, ( and function is used in store procedure to fetch reports).

ALTER   Function Fn_CalcExpAfterUtil
[COLOR="Red"](@ yymmdd [/COLOR]smalldatetime,@Mat smalldatetime , @InitialDate smalldatetime, @AuthorizeDate smalldatetime,
@TradeID varchar(50), @RiskGrade varchar(50), @ExpComm bigint, @ExpTF bigint, @Exp bigint)
Returns float
as
Begin
	Declare @RetVal float
	Declare @RatingIndex Bigint
	Declare @Draw AS Float

	If (@ExpComm = 0 And @ExpTF = 0)
		Begin
		    Set @RetVal=@Exp
		    Goto ExitRoutine
		End

	If (@ExpTF > 0)
	Begin
	    If (@TradeID <> 'LCI' And @TradeID <> 'LCL' And @TradeID <> 'LCPRC' And @TradeID <> 'LCU')
	   	    Begin
			    Set @RetVal=@Exp
			    Goto ExitRoutine
			End
	End

	If @RiskGrade = '99' Or @RiskGrade = '00' Or Len(Isnull(@RiskGrade,0)) = 1
		Begin
		    If (Len(Isnull(@InitialDate,0)) <> 0 And Len(Isnull(@AuthorizeDate,0)) <> 0)
			    Begin
				    If (@AuthorizeDate < '7/10/01')
						Begin
			            	Set @RiskGrade = '06'
						End
			        Else If (@InitialDate < '7/10/01')
						Begin
				            Set @RiskGrade = '06'
						End
			        Else
						Begin
				            Set @RiskGrade = '061'
						End
		        End
		    Else
				Begin
			        Set @RiskGrade = '06'
			    End
		End

		Set @RatingIndex = 1

	If (Len(Isnull(@InitialDate,0)) <> 0 And Len(Isnull(@AuthorizeDate,0)) <> 0)
	Begin
	    If (@AuthorizeDate < '7/10/01')
			Begin
		        Set @RatingIndex = 1
			End
	    Else If (@InitialDate < '7/10/01')
			Begin
		        Set @RatingIndex = 1
			End
	    Else
			Begin
		        Set @RatingIndex = 2
		    End
	End


	If (@RatingIndex = 2)
		Begin
		    If (@RiskGrade = '05' Or @RiskGrade = '06')
				Begin
			        Set @RiskGrade = @RiskGrade + '1'
			    End
		End 

	If (@RiskGrade = '051' Or @RiskGrade = '052' Or @RiskGrade = '061' Or @RiskGrade = '062' Or @RiskGrade = '083')
		Begin
		    Set @RatingIndex = 2
		End


	If (@RatingIndex = 1)
		Begin
			Select @Draw=(DrawRatio/100) from tblAQRExpAfterUtil where RatingIndex=1 and RiskGrade=left(@RiskGrade, 2) And [COLOR="red"]@yymmdd <1/1/2008[/COLOR]
		End
	Else
		Begin
			Select @Draw=(DrawRatio/100) from tblAQRExpAfterUtil where RISKGRADE =@RISKGRADE AND RATINGINDEX <> 1
		End



	Set @RetVal= @Exp * @Draw
	Goto ExitRoutine

ExitRoutine:
	Return @RetVal

End

Here i added parameter @ yymmdd, this has to to be the same value as of to run report from my code.( same as from cmbdatelist from above report form.
(i know even this worked this will able to view two different years report, one current year nad rest previous.)
but when i ran report it gave me eror sayin " insufficient number of arguments were supplied to procedure or function fn_calcexpafterutil"

some suggestion would be appreciated, in order to change the function.

 

[drek01]

ANy Idea, SQl sister, or anyone else help me out here, plz

 

[drek01]

Sql Sister i havenot yet come up with this..