VBA Function-> Stored Procedure!?? 4

kingz2000 · Jan 22, 2009

Hi everyone,

I have a function in VBA, which I would like to convert into Stored procedure in SQL -Server. I think I have most of it covered..Its just a For NExt loop which is completely wrong in my stored procedure. In fact I am not sure I can convert it like I am trying. I am a complete beginner in stored procedure , and hence appreciate any help.Thanks in Advance..Here is first of all the VBA function:

Private Function Sub_Unearned(D_Start As Date, D_End As Date) As Double
Dim No_Years As Long
Dim i As Long
Dim DaysPerYear As Integer

Dim Date_1 As Date
Dim Date_2 As Date

Sub_Unearned = 0
No_Years = Year(D_End) - Year(D_Start) + 1
For i = 1 To No_Years
DaysPerYear = 1 + DateSerial(Year(D_Start) + i - 1, 12, 31) - DateSerial(Year(D_Start) + i - 1, 1, 1)
Select Case i
Case 1
Date_1 = DateSerial(Year(D_Start), 12, 31)
Date_2 = D_Start
Case No_Years
Date_1 = D_End
Date_2 = DateSerial(Year(D_Start) + i - 1, 1, 1)
Case Else
Date_1 = DateSerial(Year(D_Start) + i - 1, 12, 31)
Date_2 = DateSerial(Year(D_Start) + i - 1, 1, 1)
End Select
If No_Years = 1 Then
Sub_Unearned = (D_End - D_Start + 1) / DaysPerYear
Else
Sub_Unearned = Sub_Unearned + (Date_1 - Date_2 + 1) / DaysPerYear
End If
Next i
End Function

and my attempted solution reads as follows:

CREATE FUNCTION dbo.fn_Sub_Unearned
(@D_Start datetime,@D_End datetime)
RETURNS numeric(7,0)
AS
BEGIN
declare @No_Years numeric(7,0)
declare @i numeric(7,0)
declare @Days_per_year int
declare @Date_1 as datetime
declare @Date_2 as datetime
declare @Rueckwert as numeric(7,0)
declare @MyCursor CURSOR

set @Rueckwert=0
set @No_Years=datename(year,@D_end)-datename(year,@D_start)+1

OPEN @MyCursor
FETCH NEXT FROM @MyCursor
while @@fetch_status<@No_years
begin
@Days_per_year = 1 + datediff((datename(year,@D_Start)+@i-1,'1231'),(datename(year,@D_Start)+@i-1+'0101'))
select case @i
when 1 then @Date_1=datename(year,@D_Start)+'1231'
and @Date_2= @D_Start
when @No_Years then @Date_1 =@D_End
and @Date_2 =Datename(year,@D_Start)+@i-1+'0101'
else
@Date_1=Datename(year,@D_Start)+@i-1+'1231'
@Date_2=Datename(year,@D_Start)+@i-1+'0101'
end
if @No_Years =1 then
@rueckwert = (@D_End - @D_Start+1)/@Daysperyear
else
@rueckwert = @rueckwert + (@Date_1 - @Date_2 + 1) /@Daysperyear
end if
FETCH NEXT FROM @MyCursor

return @rueckwert

END

How can I convert the loop accordingly.??

Kind regards,

Kingsley

bborissov · Jan 22, 2009

Try this (NOT TESTED)

Code:

[COLOR=blue]CREATE[/color]   [COLOR=#FF00FF]FUNCTION[/color] dbo.fn_Sub_Unearned
    (@D_Start [COLOR=#FF00FF]datetime[/color],@D_End [COLOR=#FF00FF]datetime[/color])
RETURNS [COLOR=blue]numeric[/color](7,0)
[COLOR=blue]AS[/color]
[COLOR=blue]BEGIN[/color]
    [COLOR=blue]declare[/color] @No_Years [COLOR=blue]numeric[/color](7,0)
    [COLOR=blue]declare[/color] @i [COLOR=blue]numeric[/color](7,0)
    [COLOR=blue]DECLARE[/color] @year1 [COLOR=blue]int[/color]
    [COLOR=blue]DECLARE[/color] @year2 [COLOR=blue]int[/color]

    [COLOR=blue]declare[/color] @Daysperyear [COLOR=blue]int[/color]
    [COLOR=blue]declare[/color] @Date_1 [COLOR=blue]as[/color] [COLOR=#FF00FF]datetime[/color]
    [COLOR=blue]declare[/color] @Date_2 [COLOR=blue]as[/color] [COLOR=#FF00FF]datetime[/color]
    [COLOR=blue]declare[/color] @Rueckwert [COLOR=blue]as[/color] [COLOR=blue]numeric[/color](7,0)

    [COLOR=blue]set[/color] @Rueckwert=0
    [COLOR=blue]SET[/color] @year1   = [COLOR=#FF00FF]year[/color](@D_start)
    [COLOR=blue]SET[/color] @year2   = [COLOR=#FF00FF]year[/color](@D_end)

    [COLOR=blue]set[/color] @No_Years=@year2 - @year1 + 1
    [COLOR=blue]SET[/color] @I = 1
    [COLOR=blue]while[/color] @i <= @No_years
         [COLOR=blue]begin[/color]
             [COLOR=blue]SET[/color] @Daysperyear = 1 + [COLOR=#FF00FF]datediff[/color](dd, [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color]((@year1+@i-1) [COLOR=blue]as[/color] [COLOR=blue]CHAR[/color](4))+[COLOR=red]'0101'[/color] [COLOR=blue]AS[/color] [COLOR=#FF00FF]datetime[/color]),[COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color]((@year1+@i-1) [COLOR=blue]as[/color] [COLOR=blue]CHAR[/color](4))+[COLOR=red]'1231'[/color] [COLOR=blue]AS[/color] [COLOR=#FF00FF]datetime[/color]))
             [COLOR=blue]SET[/color] @Date_1 = [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color]((@year1+@i-1) [COLOR=blue]as[/color] [COLOR=blue]CHAR[/color](4))+[COLOR=red]'1231'[/color] [COLOR=blue]AS[/color] [COLOR=#FF00FF]datetime[/color])
             [COLOR=blue]SET[/color] @Date_2 = [COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]CAST[/color]((@year1+@i-1) [COLOR=blue]as[/color] [COLOR=blue]CHAR[/color](4))+[COLOR=red]'0101'[/color] [COLOR=blue]AS[/color] [COLOR=#FF00FF]datetime[/color])
             [COLOR=blue]IF[/color] @i = @No_Years 
                [COLOR=blue]SET[/color] @Date_1 =@D_End

             [COLOR=blue]IF[/color] @No_Years =1
                [COLOR=blue]SET[/color] @rueckwert = ([COLOR=#FF00FF]DateDiff[/color](dd,@D_Start, @D_End)+1)/@Daysperyear
             [COLOR=blue]else[/color]
                [COLOR=blue]SET[/color] @rueckwert = @rueckwert + ([COLOR=#FF00FF]DateDiff[/color](dd,@Date_2, @Date_1)+1)/@Daysperyear
        
             [COLOR=blue]SET[/color] @i = @i + 1
         [COLOR=blue]END[/color] 
    [COLOR=blue]RETURN[/color] @rueckwert
[COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

kingz2000 · Jan 22, 2009

Thank you ever so much!

kingz2000 · Feb 2, 2009

ok, because of decimal places I changed the datatypes of the stored procedure to the following:

CREATE FUNCTION dbo.fn_Sub_Unearned
(@D_Start datetime,@D_End datetime)
RETURNS numeric(12,2)
AS
BEGIN
declare @No_Years numeric(12,2)
declare @i int
DECLARE @year1 int
DECLARE @year2 int

declare @Daysperyear int
declare @Date_1 as datetime
declare @Date_2 as datetime
declare @Rueckwert as numeric(12,2)

set @Rueckwert=0
SET @year1 = year(@D_start)
SET @year2 = year(@D_end)

set @No_Years=@year2 - @year1 + 1
SET @i = 1
while @i <= @No_years
begin
SET @Daysperyear = 1 + datediff(dd, CAST(CAST((@year1+@i-1) as CHAR(4))+'0101' AS datetime),CAST(CAST((@year1+@i-1) as CHAR(4))+'1231' AS datetime))
SET @Date_1 = CAST(CAST((@year1+@i-1) as CHAR(4))+'1231' AS datetime)
SET @Date_2 = CAST(CAST((@year1+@i-1) as CHAR(4))+'0101' AS datetime)
IF @i = @No_Years
SET @Date_1 =@D_End

IF @No_Years =1
SET @rueckwert = (DateDiff(dd,@D_Start, @D_End)+1)/@Daysperyear
else
SET @rueckwert = @rueckwert + (DateDiff(dd,@Date_2, @Date_1)+1)/@Daysperyear

SET @i = @i + 1
END
RETURN @rueckwert
END

However when I tried the two functions with

select dbo.fn_Sub_Unearned('20080701','20081231')
(result=0.00 from stored procedure)

and

?Sub_Unearned(#07/01/2008#,#12/31/2008#)
(result is 0,502732240437158)

CAn someone please tell me why I get 0.00 from the stored procedure instead of the accurate value?! Thanks in advance.

softhemc · Feb 2, 2009

Code:

(DateDiff(dd,@D_Start, @D_End)+1)/@Daysperyear

and

Code:

(DateDiff(dd,@Date_2, @Date_1)+1)/@Daysperyear

I think the problem is that DateDiff returns an integer and @Daysperyear is declared as an integer.

When you divide an integer by an integer you get an integer as a result.

If you cast @Daysperyear as a double (or other non integer numeric type) you should get the desired result

gmmastros · Feb 2, 2009

CAn someone please tell me why I get 0.00 from the stored procedure instead of the accurate value?!

Sure. Your problem is with integer math. If all your values are integer, then the result will be an integer too. Truth is, it took me a long time to spot the problem, but this type of exercise is good for the soul (but bad for your patience).

Anyway, your problem is here:

Code:

SET @rueckwert = (DateDiff(dd,@D_Start, @D_End)+1)/@Daysperyear
SET @rueckwert = @rueckwert + (DateDiff(dd,@Date_2, @Date_1)+1)/@Daysperyear

For now, let's replace the variables with their data type.

[tt][blue]
SET Numeric(12,2) = (DateDiff(dd,DateTime, DateTime)+1)/int
SET Numeric(12,2) = Numeric(12,2) + (DateDiff(dd,DateTime, DateTime)+1)/int
[/blue][/tt]

Next, if you look at help (Books On Line) for the DateDiff function, you'll see that it returns an integer, so let's replace that too.

[tt][blue]
SET Numeric(12,2) = (int+1)/int
SET Numeric(12,2) = Numeric(12,2) + (int+1)/int
[/blue][/tt]

The constant (1) is automatically converted to an integer by SQL Server. So, again...

[tt][blue]
SET Numeric(12,2) = (int+int)/int
SET Numeric(12,2) = Numeric(12,2) + (int+int)/int
[/blue][/tt]

Integer math, so... int + int returns an int.

[tt][blue]
SET Numeric(12,2) = int/int
SET Numeric(12,2) = Numeric(12,2) + int/int
[/blue][/tt]

More integer math.... an int divided by an int returns an int.

[tt][blue]
SET Numeric(12,2) = int
SET Numeric(12,2) = Numeric(12,2) + int
[/blue][/tt]

That's probably far enough. You see, your numerator was smaller than your denominator, so you were expecting a value less than 1. Since you have an integer math problem, the result was 0. Then, when you set this to a numeric(12,2) (or add it to a numeric(12,2)), it's already 0, so your output is 0.

Change your code to this:

Code:

             IF @No_Years =1
                SET @rueckwert = [!]Convert[/!]([!]Numeric(12,2),[/!] DateDiff(dd,@D_Start, @D_End)+1)/@Daysperyear
             else
                SET @rueckwert = @rueckwert + [!]Convert[/!]([!]Numeric(12,2),[/!] DateDiff(dd,@Date_2, @Date_1)+1)/@Daysperyear

Last thing.... after changing the code, you will still not get the same number that you did in VBA. I think you are confused about the precision and scale with numerics. The largest value that a Numeric(12,2) can hold is 9999999999.99 and the smallest is -9999999999.99. The first number identifies the total number of digits before and after the decimal point. The second number controls how many numbers after the decimal point. My point is... you'll probably want to change it.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

gmmastros · Feb 2, 2009

softhemc,

My apologies. You already identified the problem. Had I seen your response before posting, I wouldn't have bothered.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

kingz2000 · Feb 5, 2009

Hi again,

I thought I had this function working when I tried it with
select dbo.fn_Sub_Unearned('20080101','20090101')
I did get the correct answer 1.0
Unfortunately, when I tested it using middle of the year value
select dbo.fn_Sub_Unearned('20080701','20090701')
I got 1.5 whereby the VBA version gave me the correct 1.0
I have also changed the datatype definition to numeric(5.2) as requested.Aswell as changing the code with Convert(Numeric as requested. My current version is as below:

CREATE FUNCTION dbo.fn_Sub_Unearned
(@D_Start datetime,@D_End datetime)
RETURNS numeric(5,2)
AS
BEGIN
declare @No_Years numeric(5,2)
declare @i int
DECLARE @year1 int
DECLARE @year2 int

declare @Daysperyear as numeric(5,2)
declare @Date_1 as datetime
declare @Date_2 as datetime
declare @Rueckwert as numeric(5,2)

set @Rueckwert=0
SET @year1 = year(@D_start)
SET @year2 = year(@D_end)

set @No_Years=@year2 - @year1 + 1
SET @i = 1
while @i <= @No_years
begin
SET @Daysperyear = 1 + datediff(dd, CAST(CAST((@year1+@i-1) as CHAR(4))+'0101' AS datetime),CAST(CAST((@year1+@i-1) as CHAR(4))+'1231' AS datetime))
SET @Date_1 = CAST(CAST((@year1+@i-1) as CHAR(4))+'1231' AS datetime)
SET @Date_2 = CAST(CAST((@year1+@i-1) as CHAR(4))+'0101' AS datetime)
IF @i = @No_Years
SET @Date_1 =@D_End

IF @No_Years =1
SET @rueckwert=Convert(Numeric(5,2),DateDiff(dd,@D_Start, @D_End)+1)/@Daysperyear
else
SET @rueckwert = @rueckwert +Convert(Numeric(5,2),DateDiff(dd,@Date_2, @Date_1)+1)/@Daysperyear

SET @i = @i + 1
END
RETURN @rueckwert
END

What else do I need to change?? Shall I change all the int datatypes to numeric(5,2) to be sure??
Thanks in advance for help.

Oh, By the way, I thought I'd change all my integer values to numeric(5,2) aswell to be on the safe side, but that gave me 'Arithmetic overflow error converting numeric to data type numeric.'

SimonSellick · Feb 5, 2009

Your SQL Server function seems to be missing the

Code:

case 1 
  Date_1 = DateSerial(Year(D_Start), 12, 31)
  Date_2 = D_Start

equivalent from your VB sub - the case where it's the first year, and you should start from D_Start instead of 01/01.

Also, its more accurate (as softhemc said) to use double arithmetic throughout if you have any non-exact operations (division and clever stuff) then just convert (or cast) the result to the precision that you want.

Hope this helps.

kingz2000 · Feb 17, 2009

sorry, but how can I build this special case into the while loop?? My knowledge of stored procedures is abysmal. Is it possible at all for me to create a store procedure which gives me the same values to 2 decimal places? Thats all I want - Please!

Kind regards,

Kingsley

SimonSellick · Feb 17, 2009

I've translated your VB function line-by-line to T-SQL so that you can see the correspondence between the various elements. As you spotted, the integer arithmetic in the result assignment would cause truncation, so I've casat one part (number of years) to a float to force the correct type of intermediate result. The VB is in the comments, but it makes the page a bit wide - you might need to paste it into an editor to see it properly.
[wide]

Code:

drop function Sub_Unearned
go

create function Sub_Unearned (						-- Private Function Sub_Unearned(D_Start As Date, D_End As Date) As Double
  @D_Start smalldatetime
, @D_End smalldatetime
) returns float as
begin
  declare @No_Years int							-- Dim No_Years  As Long
  declare @i int							-- Dim i As Long
  declare @DaysPerYear int						-- Dim DaysPerYear As Integer

  declare @Date_1 smalldatetime						-- Dim Date_1 As Date
  declare @Date_2 smalldatetime						-- Dim Date_2 As Date

  declare @Sub_Unearned float

  set @Sub_Unearned = 0							-- Sub_Unearned = 0

  set @No_Years = datepart(year, @D_End) - datepart(year, @D_Start + 1)	-- No_Years = Year(D_End) - Year(D_Start) + 1

  set @i = 1
  while @i <= @No_Years begin						-- For i = 1 To No_Years
    set @DaysPerYear = 1 + datediff(day,				--   DaysPerYear = 1 + 
      convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/12/31')	--   DateSerial(Year(D_Start) + i - 1, 12, 31) - 
    , convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/01/01')	--   DateSerial(Year(D_Start) + i - 1, 1, 1)
    )
									--   Select Case i
    if @i = 1 begin							--   Case 1
      set @Date_1 = convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/12/31')	--     Date_1 = DateSerial(Year(D_Start), 12, 31)
      set @Date_2 = @D_Start						--     Date_2 = D_Start
    end else if @i = @No_Years begin					--   Case No_Years
      set @Date_1 = @D_End						--     Date_1 = D_End
      set @Date_2 = convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/01/01')	--     Date_2 = DateSerial(Year(D_Start) + i - 1, 1, 1)
    end else begin							--   Case Else
      set @Date_1 = convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/12/31')	--     Date_1 = DateSerial(Year(D_Start) + i - 1, 12, 31)
      set @Date_2 = convert(smalldatetime
      , convert(varchar, datepart(year, @D_Start + @i - 1)) + '/01/01')	--     Date_2 = DateSerial(Year(D_Start) + i - 1, 1, 1)
    end									--   End Select
    if @No_Years = 1							--   If No_Years = 1 Then
      set @Sub_Unearned = (datediff(day, @D_End, @D_Start) + 1)
                        / cast(@DaysPerYear as float)			--     Sub_Unearned = (D_End - D_Start + 1) / DaysPerYear
    else								--   Else
      set @Sub_Unearned = @Sub_Unearned
                        + (datediff(day, @Date_1, @Date_2) + 1) 
                        / cast(@DaysPerYear as float)			--     Sub_Unearned = Sub_Unearned + (Date_1 - Date_2 + 1) / DaysPerYear
									--   End If
    set @i = @i + 1							-- Next i
  end		

  return @Sub_Unearned							-- End Function
end
go

-- end

Hope this helps.

kingz2000 · Feb 17, 2009

FANTASTIC

ESquared · Feb 20, 2009

I believe this function will perform significantly faster:

Code:

CREATE FUNCTION dbo.Unearned (
   @StartDate datetime,
   @EndDate datetime
)
RETURNS numeric(27, 12)
AS
BEGIN
   RETURN (
      SELECT
         DateDiff(dd, @StartDate, FromYear2) * 1.0 / DateDiff(dd, FromYear1, FromYear2)
         + DateDiff(dd, EndYear1, @EndDate) * 1.0 / DateDiff(dd, EndYear1, EndYear2)
         + DateDiff(yy, @StartDate, @EndDate) - 1
      FROM
         (
            SELECT
               FromYear1 = DateAdd(yy, DateDiff(yy, 0, @StartDate), 0),
               FromYear2 = DateAdd(yy, DateDiff(yy, 0, @StartDate) + 1, 0),
               EndYear1 = DateAdd(yy, DateDiff(yy, 0, @EndDate), -1),
               EndYear2 = DateAdd(yy, DateDiff(yy, 0, @EndDate) + 1, -1)
         ) X
   )
END

ESquared · Feb 20, 2009

By the way, SimonSellick's function gives wrong values. Try these as input:

StartDate, EndDate
2008-07-01, 2008-09-01
2008-07-01, 2010-07-01

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

VBA Function-> Stored Procedure!?? 4

kingz2000

Programmer

bborissov

Programmer

kingz2000

Programmer

kingz2000

Programmer

softhemc

Programmer

gmmastros

Programmer

gmmastros

Programmer

kingz2000

Programmer

SimonSellick

Programmer

kingz2000

Programmer

SimonSellick

Programmer

kingz2000

Programmer

ESquared

Programmer

ESquared

Programmer

Similar threads

Part and Inventory Search

Sponsor

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

VBA Function-&gt; Stored Procedure!?? 4

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor

VBA Function-> Stored Procedure!?? 4