Hi all...
So, I am needing to sum all billable hours within a table... the problem is that it is returning rouded decimals instead... like .5 always comes back as 1 ...
Here is my procedure:
Public Shared Function GetBillableTimeForTicket(ByVal SupportTicketID As Integer) As Decimal
Dim conn As New SqlConnection(GetConnectionString("ClientPortal.My.MySettings.conString"))
Dim sP As String = "GetBillableTimeForTicket"
Dim cmd As New SqlCommand()
cmd.Parameters.Clear()
cmd.CommandText = sP
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SupportTicketID", SupportTicketID)
Dim parameter1 As New SqlParameter("@TotalBillableTime", SqlDbType.Decimal)
parameter1.Direction = ParameterDirection.Output
cmd.Parameters.Add(parameter1)
cmd.Connection = conn
Try
HandleConnection(conn)
cmd.ExecuteNonQuery()
Try
Dim totalBillableTime As Decimal = Decimal.Parse(cmd.Parameters("@TotalBillableTime").Value.ToString())
Return totalBillableTime
Catch ex As Exception
MsgBox(ex.Message)
End Try
Catch ex As Exception
MsgBox(ex.Message)
Finally
HandleConnection(conn)
End Try
End FunctionAnd here is my StoredProcedure... I have tried so many variations of this SUM... Here is my latest...
ALTER PROCEDURE dbo.GetBillableTimeForTicket
(
@SupportTicketID int,
@TotalBillableTime decimal (18,2) output
)
As
Set NoCount On;
SELECT @TotalBillableTime = ROUND(SUM(SupportTicketResponseBillableTime),2) FROM SupportTicketResponses WHERE SupportTicketID = @SupportTicketID;Any help would be greatly appreciated, thank you in advance... Oh BTW... I'm using SQL 2008 with .NET 3.5
