How to sum up registered hours between two dates (

Question

I couldn't find questions matching mine. If you know one, please let me know.

I need a select statement that shows me the total hours of absence for an employee between to dates.

Example:

Employee Dennis had absence from 2014-06-10 to 2014-06-13. This employee had different absence hours between the dates. Lets say that Dennis have been absent like this:

2014-06-10: 477 minutes
2014-06-11: 498 minutes
2014-06-12: 491 minutes
2014-06-13: 397 minutes
total absence: 1863 minutes

I want to get an output like this:

I have the tables
EmployeeTime that have the hours of absence per day.
AbsenceDays that have the from and till date for a absence.

EmployeeTime would look like this:

| EmployeeName | Date | RegisteredHour |

and AbsenceDays would look like this:

| FromDate | TillDate | AbsenceId | KeyNo |

I have a third table LogTable that can link these two tables together by KeyNo and EmployeeId.

The output I get:

| EmployeeName | FromDate   | TillDate   | Days |Days without weekends | Date       | RegisteredHour | Total min absence
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-10 | 477            | 9540
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-11 | 498            | 9960
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-12 | 491            | 9960
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-13 | 397            | 7940
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-09 | 420            | 2100
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-10 | 0              | 0
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-11 | 0              | 0
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-12 | 450            | 2250

I need to get this result:

| EmployeeName | FromDate   | TillDate   | Days |Days without weekends | Total min absence
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 1863
| Joanne       | 2014-06-09 | 2014-06-12 | 4    |        2             | 870

My code:

SELECT DISTINCT 
    EmployeeTime.Name,
    CONVERT(VARCHAR(10),FromDate,20) FromDate,
    CONVERT(VARCHAR(10),Tilldate,20) Tilldate,
    (DATEDIFF(DD, FromDate, Tilldate) +1) TotalDays,
    (DATEDIFF(DD, FromDate, Tilldate) +1) - (DATEDIFF(wk, FromDate, Tilldate) * 2) DaysMinusWeekends,
    CONVERT(VARCHAR(10),Dato,105) DateDato,
    RegisteredHour,
    SUM(RegisteredHour) Total
FROM EmployeeTime, AbsenceDays, LogTable
WHERE EmployeeTime.EmployeeId = LogTable.EmployeeId
    AND LogTable.KeyNo = AbsenceDays.KeyNo
    AND EmployeeTime.EmployeeId = 'D994'
    AND (DATEDIFF(DD, FromDate, TillDate) +1) = 4
    AND CONVERT(VARCHAR(10),FromDate,20) <= CONVERT(VARCHAR(10),Dato,20)
    AND CONVERT(VARCHAR(10),TillDate,20) >= CONVERT(VARCHAR(10),Dato,20)
    GROUP BY EmployeeTime.EmployeeId,FromDate, TillDate, Dato, RegisteredHour
    ORDER BY EmployeeTime.EmployeeId

Note: I need an answer where you don't self-join tables or use WITH queries; SELECT has to be the first thing in the SQL or the program won't understand it.

Answer 1

This is my best guess at an answer. It probably won't work as-is for you because: (1) I don't know the full table schema for all your tables; (2) I don't understand what you mean by "I need an answer where you don't join tables"; (3) I only know PostgreSQL, not SQL Server.

In English, what you want to do is pick a row of AbsenceDays and then use the FromDate and TillDate to select a set of rows in EmployeeTime, and then sum the RegisteredHour column in those rather than the absolute date difference. The most obvious way to do that is

SELECT et.EmployeeName, ad.FromDate, ad.TillDate, SUM(et.RegisteredHour)

  FROM LogTable lt
  JOIN EmployeeTime et ON lt.EmployeeId = et.EmployeeId
  JOIN AbsenceDays ad  ON lt.KeyNo      = ad.KeyNo

 WHERE et.Date >= ad.FromDate
   AND et.Date <= ad.TillDate
-- AND et.EmployeeName = 'Dennis'

 GROUP BY et.EmployeeName, ad.FromDate, ad.TillDate
 ORDER BY et.EmployeeName, ad.FromDate;

Looking back at your code, it seems like you have essentially this embedded in there, but obfuscated with a lot of CONVERTs and DATEDIFFs that seem unnecessary to me. That may be because, again, I only know PostgreSQL. I recommend you strip your code down to a bare minimum, like this, and then add conversions back in until the database accepts it.

Another potential problem would be if (EmployeeName, FromDate, TillDate) doesn't uniquely identify one continuous absence. (You seem to have two ID columns in AbsenceDays; that suggests this might be an issue.) You also talk inconsistently of minutes and hours, so I don't know which you actually want. And finally, I have no idea how to compute the number of non-weekend days.