My company business hour is 7:30 to 18 and 13:00 to 15:00 is lunch time. Important part is about lunch time that should not calculate as working time of employee at all.
My fields are all in Time(7) format in Sql.
On
Alas you forgot to give us the structure of you database table. Are your EnterTime and ExitTime full DateTimes, or do you have a WorkDay, and a TimeOfDay on that day where the Employee Starts and Stops working?
Besides that you forgot to give us your table structure, I don't know how you communicate with your database, whether you use entity framework, or talk traditional SQL, and which SQL dialect you speak. Some dialects have the possibility to do DateTime calculations, others don't.
The comment of MadReflection is wise: if you can change your database, consider to remember the start and stop DateTime of the shift before lunch and the start / stop DateTime of the shift after lunch. This way you are prepared for future changes like different lunch times per Employee, or even per day of the Employee. If you think in DateTime for Enter and Exit, you can even let an Employee start working on New Year's eve and stop working in a different year.
Luckily, you don't have to let the DataBase calculate the lunchTime, because you can do the lunch time calculations locally. This is faster than letting the database management system calculate them and transfer the results from the DBMS to your local process.
Locally the lunch time calculations will just a subtraction of two longs, which is way faster than the code needed to transfer the data.
So we can't give you the SQL. You'll have to write the procedure to fetch the data yourself.
You'll need a method to fetch the EnterTime / ExitTime per Employee during a period of time, or maybe for all Employees. The result is something like:
class WorkingTimes
{
public EmployeeInformation Employee {get; set;}
public DateTime EnterTime {get; set;}
public DateTime ExitTime {get; set;}
}
And methods to get this information per employee, or for all employees:
IEnumerable<WorkingTimes> FetchWorkingTimes(DateTime beginPeriod, DateTime endPeriod)
{
...
}
IEnumerable<WorkingTimes> FetchWorkingTimes(int employeeId,
DateTime beginPeriod, DateTime endPeriod)
{
...
}
To convert the fetched data in the format that you want, a simple LINQ method will do:
class WorkingTimeExt
{
// TODO: if needed: add employee information
public System.DayOfWeek DayOfWeek => this.EnterTime.DayOfWeek;
public DateTime EnterTime {get; set;}
public DateTime ExitTime {get; set;}
public DateTime StartLunch {get; set;}
public DateTime StopLunch {get; set;}
public string Lunch => String.Format("...", this.StartLunch, this.StopLunch);
public TimeSpan WorkingTime => this.StartLunch - this.EnterTime
+ this.ExitTime - this.StopLunch;
}
The calculation of WorkingTime is fast, it is the addition of some longs.
TimeSpan defaultLunchStart = TimeSpan.FromHours(13.00);
TimeSpan defaultLunchEnd = TimeSpan.FromHours(15.00);
IEnumerable<WorkingTimes> fetchedWorkingTimes = ...
IEnumerable<WorkingTimesExt> result = fetchedWorkingTimes
.Select(fetchedTimes => new WorkingTimesExt
{
EnterTime = fetchedWorkingTimes.EnterTime,
ExitTime = fetchedWorkingTimes.ExitTime,
StartLunchTime = fetchedWorkingTime.EnterTime.Date + defaultLunchStart,
StopLunchTime = fetchedWorkingTime.ExitTime.Data + defaultLunchStop,
});
If you need to do this regularly, consider to create an extension method for it:
IEnumerable<WorkingTimesExt> result = ToExtendedWorkingTimes(
this IEnumerable<WorkingTimes> source,
TimeSpan timeLunchStart,
TimeSpan timeLunchStop)
{
return source.Select(fetchedTimes => new WorkingTimesExt
{
EnterTime = fetchedWorkingTimes.EnterTime,
ExitTime = fetchedWorkingTimes.ExitTime,
StartLunchTime = fetchedWorkingTime.EnterTime.Date + timeLunchStart,
StopLunchTime = fetchedWorkingTime.ExitTime.Data + timeLunchStop,
});
}
I am posting this code to solve your problem
TimeSpan? ActualTime = obj.ExitTime - obj.EnterTime;
string[] LunchTime = obj.lunchTimeString.Split('-');
TimeSpan ActualLunchTime = TimeSpan.Parse(LunchTime[1].Trim()) - TimeSpan.Parse(LunchTime[0].Trim());
TimeSpan WorkingHours = TimeSpan.Parse(ActualTime.ToString().Trim()) - TimeSpan.Parse(ActualLunchTime.ToString().Trim());
In this code what I have done is:
On
Another worked option with time worked converted to HH:MM format:
CREATE TABLE t1 (dow VARCHAR(10), workstart TIME, workend TIME);
INSERT INTO t1 VALUES
('Monday', '7:30', '18:00'),
('Tuesday', '12:00', '15:00'),
('Wednesday', '15:00', '18:00'),
('Thursday', '8:30', '15:00');
WITH
lunch(lunchstart, lunchend) AS (
SELECT CAST('13:00' AS TIME), CAST('15:00' AS TIME)
),
attendances AS (
SELECT *,
CASE WHEN workstart <= lunchstart THEN
CASE WHEN workend <= lunchstart THEN
DATEDIFF(minute, workstart, workend)
ELSE
DATEDIFF(minute, workstart, lunchstart)
END
ELSE 0 END --AS morning_shift
+
CASE WHEN workend >= lunchend THEN
CASE WHEN workstart >= lunchend THEN
DATEDIFF(minute, workstart, workend)
ELSE
DATEDIFF(minute, lunchend, workend)
END
ELSE 0 END --AS afternoon_shift
AS total_mins
FROM t1, lunch
)
SELECT *, FORMAT(total_mins / 60 * 100 + total_mins % 60, '#:0#') AS hhmm
FROM attendances;
This is one approach in SQL Server. It fundamentally splits each person's shift into two - Pre-Lunch and Post-Lunch. When a shift goes into (or past) lunch, it uses the lunch times as the bounds instead.
I've also written it in bits (e.g., CTE, sub-query, etc) so you can run those individually to see what they do. It is likely you'll need to update it for your own database structures etc.
Here's a db<>fiddle
Issues: