We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

# How to calculate the Total Working Hours and Total Day in my Query[Answered] RSS

## 3 replies

Last post Dec 08, 2011 04:12 AM by Chen Yu - MSFT

• shahid.majee...

Member

297 Points

940 Posts

### How to calculate the Total Working Hours and Total Day in my Query

Hi,

I am calculating total working hours of the employee. my database table have the following Columns, Attendance Date, Start Time, End Time, Over Time, and UserName

Here is my SQL Query

```Select AttendanceDate,StartTime,EndTime,DATEDIFF(Hour,StartTime,EndTime)As WorkingHours
From KingsUKAttendance```

Here Is my Query Result

`AttendanceDate	                 StartTime	                 EndTime                  WorkingHours`
```2011-12-03 00:00:00.000	08:00:00.0000000	12:00:00.0000000	4
2011-12-03 00:00:00.000	08:00:00.0000000	15:00:00.0000000	7
2011-12-04 00:00:00.000	10:00:00.0000000	18:00:00.0000000	8
2011-12-03 00:00:00.000	15:00:00.0000000	16:00:00.0000000	1```

Now I want to calculate the Total Working Hours and Total Days in my query.

• jassi_singh

Participant

891 Points

408 Posts

### Re: How to calculate the Total Working Hours and Total Day in my Query

Hello,

Do you want to calculate the total working hours for a day? or for the entire month for a particular employee?

• shahid.majee...

Member

297 Points

940 Posts

### Re: How to calculate the Total Working Hours and Total Day in my Query

Here is my Update Query

```Select AttendanceDate,StartTime,EndTime,WorkingHours, OverTime
From KingsUKAttendance
Order by AttendanceDate```

I try to calculate the Working hours for a specific Employee for specific dates.

This Query Give me this Result

```AttendanceDate	StartTime	EndTime	WorkingHours	OverTime
2011-12-03 00:00:00.000	08:00:00.0000000	12:00:00.0000000	4	00:00:00.0000000
2011-12-03 00:00:00.000	08:00:00.0000000	15:00:00.0000000	7	00:00:00.0000000
2011-12-03 00:00:00.000	15:00:00.0000000	16:00:00.0000000	1	00:00:00.0000000```

• Chen Yu - MS...

All-Star

21620 Points

2496 Posts

### Re: How to calculate the Total Working Hours and Total Day in my Query

Dec 08, 2011 04:12 AM|Chen Yu - MSFT|LINK

Hi shahid.majeed,

Would you please tell us the result you want?  If you want to calculate the Working Hours for one day in specific dates, your query works fines. If you want to calculate the total Working Hours in specific dates, please check below query:

```declare @kingukattendance table(AttendanceDate datetime,StartTime time,EndTime time,
insert @kingukattendance values('2011-12-03','08:00:00','12:00:00',0,'A')
insert @kingukattendance values('2011-12-03','08:00:00','15:00:00',0,'A')
insert @kingukattendance values('2011-12-04','10:00:00','18:00:00',0,'A')
insert @kingukattendance values('2011-12-03','15:00:00','16:00:00',0,'A')

declare @StartDate datetime
declare @EndDate datetime