Introduction Recently, I came across the need to get the total hours worked of a person, in one shift, 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 TotalHoursWork 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).
[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();
}
in the another 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. |