Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98

    Lightbulb Unanswered: Converting stored procedure outcome

    Hi all,

    i have an stored procedure that returns a value, i want to convert that value into smallmoney, is this possible and i yes how?

    here is my code:

    Code:
    CREATE proc CP_avgloss_total
    @mID varchar(10),
    @startdate datetime,
    @enddate datetime 
    
    as 
    select 
    
    	case 
    
    		((sum(playtime))/ 3600)
    		when 0 
    		then 0
    		else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) 
    	end avgloss  
    from dbo.total
    where 
    	machineID = @mID 
    	and convert(varchar,njdate,121) 
    		between convert(varchar,@startdate,121) 
    		and convert(varchar,@enddate,121)
    
    GO
    The value that needs to convert is avgloss

    Hope someone can help me with this.
    Cheers Wim
    Last edited by Wimmo; 01-21-04 at 07:44.

  2. #2
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    select cast(P1.avgloss as smallmoney) as avgloss from

    (select

    case

    ((sum(playtime))/ 3600)
    when 0
    then 0
    else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
    end avgloss
    from dbo.total
    where
    machineID = @mID
    and convert(varchar,njdate,121)
    between convert(varchar,@startdate,121)
    and convert(varchar,@enddate,121)) P1

  3. #3
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by marp
    select cast(P1.avgloss as smallmoney) as avgloss from

    (select

    case

    ((sum(playtime))/ 3600)
    when 0
    then 0
    else ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600)
    end avgloss
    from dbo.total
    where
    machineID = @mID
    and convert(varchar,njdate,121)
    between convert(varchar,@startdate,121)
    and convert(varchar,@enddate,121)) P1
    It worked thanx but i still get as outcome 4 digit behind the break
    Do you know how to get 2 digits?

    Thanx

  4. #4
    Join Date
    Jan 2004
    Location
    Romania - Bucharest
    Posts
    50
    As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.

  5. #5
    Join Date
    Oct 2003
    Location
    Netherlands
    Posts
    98
    Originally posted by marp
    As far as I know, the smallmoney data type uses by default 4 decimal digits. If different number of decimal places are needed, "Books Online" recommends using the Decimal data type.
    Thanx Man this is everything i need.
    Your help was fantastic!
    Kind regards Wim

  6. #6
    Join Date
    Apr 2009
    Location
    USA
    Posts
    1

    Hi from Florida!

    Just wanted to say hi since I'm new here!
    'm happy to become a member of this community!!!
    Hoping for the the best

Posting Permissions

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