Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    54

    Unanswered: format results of division

    I have a function that divides the results of 2 seperate datafiffs to provide a ratio. For some reason I cannot get the result to return as a decimal - it is only giving me integers. I have tried cast and converting both the idividual numbers and the results, no luck. If I write the query on it's own, casting the one of the values as decimal will return the right value. Any ideas what I am missing? Here is the function:

    Code:
    Create function fn_FTE(
    @tkpr varchar(5),
    @start datetime,
    @end datetime)
    
    Returns decimal
    Begin
    declare
    
    @fte decimal
    
    
    select @fte = 
     
    sum(datediff(dd, 
     (case
    when eedatebeg < @start then @start
    else eedatebeg
    end), 
    (case
    when eedateend > @end then @end
    when eedateend is null then @end
    else eedateend
    end))) /datediff(dd,@start,@end)
    
    from vw_employ_dates
    where 
     eedatebeg < @end
    and (eedateend > @start or eedateend is null)
    and eudescrip = @tkpr
    
     
    return @FTE
    end
    thanks for any help!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    you can do it either explicitly or implicitly. Explicitly means that you CAST one of your DATEDIFF's as dec(...). To do it implicitly multiply one of the diffs by 1.0
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're using integer arithmetic, so of course you get an integer answer

    try this --

    select @fte = 1.00 * ... the rest of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    argh, sniped by a full two minutes, while i was typing...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Posts
    54
    I have tried cast one or both of the diffs, and the multiply by 1.0 - both will work in a query, but when I turn the query into a function, it goes back to the integer - any idea why?

  6. #6
    Join Date
    Nov 2003
    Posts
    54
    Quote Originally Posted by r937
    you're using integer arithmetic, so of course you get an integer answer

    try this --

    select @fte = 1.00 * ... the rest of it
    Sorry - still doesn't go. This is one of those things that seems so simple...

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That sounds like magic...but magic doesn't exist in computers (or shouldn't rather)...Just spent time creating a prototype of your function, and checked different scenarios. I was right on the money, so try again.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Code:
    if object_id('dbo.fn_FTE') is not null
    drop function dbo.fn_FTE
    go
    create function dbo.fn_FTE (
    @p1 datetime
    ,@p2 datetime ) returns dec(18,5)
    as begin
    declare @Result dec(18,5)
    select @Result = 
    sum(datediff(ss,(
    case when backup_start_date < @p1 then @p1 else backup_start_date end
    ), (
    case when backup_finish_date > @p2 
    then @p2 when backup_finish_date isnull 
    then @p2 else backup_finish_date 
    end
    ))) * 1.0 / datediff(dd, @p1, @p2)
    from msdb.dbo.backupset
    where backup_start_date < @p2
    and (backup_finish_date > @p1 or backup_finish_date isnull)
    return @Result
    end
    go
    select dbo.fn_FTE('01/01/2007', '12/31/2008')

    see the bolded and underlined "* 1.0" after the numerator
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, my attempt was ineffective because i applied the * 1.00 to the SUM, which is long after the damage had already been done
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2003
    Posts
    54
    Thanks a ton for your help! I think I was applying the multiplier to the sum as well, so I was missing the boat. I really appreciate you guys taking the time to help out, what seems simple to you was a very frustrating afternoon for me!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •