recently, I came acroos the need to get the total hours in sql. by using the CLR user defined functions freature, I was able to use this function into SQL server. The nice thing, is that I unit tested the function seperately, then used it. example :
SQL:
select dbo.GetTotalHours(TableName.StartTime, TableName.EndTime, TableName.Pause) as TotalHoursWorked;
The function is listed below with comments. also, below is a function for splitting the hour string.
example : GetTotalHours("20:00", "06:00", 120); Important: the pause is expressed in minutes (in this case, it's 120 minutes pause, or 2 hours of pause).
This function, inside an sql server project is created with the sql server attribute of [SqlFunction]. This tells sql server to execute this function
as an extension to sql server, which will make it run as any other sql function.
[Microsoft.SqlServer.Server.SqlFunction]
public string GetTotalHours(string StartTime, string EndTime, int Pause)
{
if (StartTime == EndTime)
{
if ((StartTime == null) || (EndTime == null) || (StartTime == "00:00") || (EndTime == "00:00"))
{
StartTime = "0:0";
EndTime = "0:0";
return "00:00";
}
}
//this split gives the following example StartTime 10:30 Start[0] gives 10 and start[1] gives 30
string[] Start = this.SplitTimeString(StartTime);
string[] End = this.SplitTimeString(StartTime);
//timespan for the taking into consideration intervals similar to :
//starttime: 20:00 and endtime 01:30
TimeSpan tsTwentyFourHours = new TimeSpan(24, 0, 0);
//start time
TimeSpan tsStart = new TimeSpan(int.Parse(Start[0]), int.Parse(Start[1]), 0);
//end time
TimeSpan tsEnd = new TimeSpan(int.Parse(End[0]), int.Parse(End[1]), 0);
TimeSpan tsPause = new TimeSpan(0, Pause, 0);
TimeSpan tsHoursWorked;
if (int.Parse(End[0]) < int.Parse(Start[0]))
{
//Case when the it's overnight work like 20:00 to 01:30
//we substract start time from twenty for hours, then add the end time.
//this gives us the correct measures
tsHoursWorked = tsTwentyFourHours.Subtract(tsStart).Add(tsEnd);
//substratct the pause
tsHoursWorked = tsHoursWorked.Subtract(tsPause);
}
else
{
//normal hours example: 08:00 to 20:00
//we simply substract the start time plus the pause from the end time
tsHoursWorked = tsEnd.Subtract(tsStart.Add(tsPause));
}
return tsHoursWorked.TotalHours.ToString() + ":" + tsHoursWorked.TotalMinutes.ToString();
}
This function is used in the function above.
public string[] SplitTimeString(string Time)
{
char[] ch = new char[] { ':', '.' };
return Time.Split(ch);
}
in the next article, will see how this function can be used upgraded to be used in sql server 2008, taking advantage of its Time datatype, using Visual Studio 2010 beta version.
Comments are welcome !
