SUM time intervals...AGGREGATE DAY... then WEEK

I would suggest that you restructure your table into

Username
WorkDate
TimeIn
TimeOut

This way daily and weekly subtotals can be calculated easily.

I agree with suggestion from aramxxii.

If you can't change structure of this table the following query may help:

select
datepart(YYYY,InMONamHour) YEAR_N,
datepart(WK,InMONamHour) WEEK_N,
convert(datetime,
  sum(convert(decimal(12,6), OutMONamHour - InMONamHour))
+ sum(convert(decimal(12,6), OutTUEamHour - InTUEamHour))
+ sum(convert(decimal(12,6), oUTWEDamHour - InWEDamHour))
+ sum(convert(decimal(12,6), OutTHURamHour - InTHURamHour))
+ sum(convert(decimal(12,6), OutFRIamHour - InFRIamHour))
+ sum(convert(decimal(12,6), OutSATamHour - InSATamHour))
+ sum(convert(decimal(12,6), OutSUNamHour - InSUNamHour))
) MORNING_HOURS,
convert(datetime,
  sum(convert(decimal(12,6), OutMONpmHour - InMONpmHour))
+ sum(convert(decimal(12,6), OutTUEpmHour - InTUEpmHour))
+ sum(convert(decimal(12,6), oUTWEDpmHour - InWEDpmHour))
+ sum(convert(decimal(12,6), OutTHURpmHour - InTHURpmHour))
+ sum(convert(decimal(12,6), OutFRIpmHour - InFRIpmHour))
+ sum(convert(decimal(12,6), OutSATpmHour - InSATpmHour))
+ sum(convert(decimal(12,6), OutSUNpmHour - InSUNpmHour))
) AFTERNOON_HOURS,
convert(datetime,
  sum(convert(decimal(12,6), OutMONamHour - InMONamHour))
+ sum(convert(decimal(12,6), OutTUEamHour - InTUEamHour))
+ sum(convert(decimal(12,6), oUTWEDamHour - InWEDamHour))
+ sum(convert(decimal(12,6), OutTHURamHour - InTHURamHour))
+ sum(convert(decimal(12,6), OutFRIamHour - InFRIamHour))
+ sum(convert(decimal(12,6), OutSATamHour - InSATamHour))
+ sum(convert(decimal(12,6), OutSUNamHour - InSUNamHour))
+ sum(convert(decimal(12,6), OutMONpmHour - InMONpmHour))
+ sum(convert(decimal(12,6), OutTUEpmHour - InTUEpmHour))
+ sum(convert(decimal(12,6), oUTWEDpmHour - InWEDpmHour))
+ sum(convert(decimal(12,6), OutTHURpmHour - InTHURpmHour))
+ sum(convert(decimal(12,6), OutFRIpmHour - InFRIpmHour))
+ sum(convert(decimal(12,6), OutSATpmHour - InSATpmHour))
+ sum(convert(decimal(12,6), OutSUNpmHour - InSUNpmHour))
) TOTAL_HOURS,
FROM nonexempt
WHERE username = username AND (ID) = (select max(ID) from nonexempt)
GROUP BY datepart(YYYY,mld_melding_geaccepteerd), datepart(WK,mld_melding_geaccepteerd)

It converts the resulting values into datetime. It's not necessary.

Oops, group clause by must be like the following:
GROUP BY datepart(YYYY,InMONamHour), datepart(WK,InMONamHour)

this query works correctly when employees work each Monday.

Very good, thanks for the prompt reply.

The query did not return any errors, but it also did not return the proper values...

This is what it returned:

YEAR_N WEEK_N MORNING_HOURS AFTERNOON_HOURS TOTAL_HOURS
1899 52  

The field property is set to datetime with a length of 8.    

Any ideas?

try using datediff to determine the number of minutes between the beginning and end of each shift - sum them and then convert to hours by dividing by 60



select
datepart(YYYY,InMONamHour) YEAR_N,
datepart(WK,InMONamHour) WEEK_N,
convert(decimal,(
      datediff(mi, inMONamHour , outMONamHour)
     + datediff(mi,  inTUEamHour ,outTUEamHour)
     + datediff(mi,  inWEDamHour , outWEDamHour)
     + datediff(mi,  inTHURamHour , outTHURamHour)
     + datediff(mi,  inFRIamHour , outFRIamHour)
     + datediff(mi,  inSATamHour , outSATamHour)
     +datediff(mi,  inSUNamHour , outSUNamHour)
)/60)as MORNING_HOURS..... and so on

How did you set length for datetime column? It has no such property.
Could you give me an example of nMONamHour value?

Yes, the feild (InMONamHour) is set as datetime, width of 8 char and it allows nulls

I am pasing a value from the form i.e., 17:00 would equal 5pm and so on...

Have I made a error here?

Thanks for your interest. JV

I have tried this and it works fine...alas, without SUMing my week:

SELECT
CONVERT(CHAR(8),OutMonamHour - InMONamHour + OutMONpmHour - InMONpmHour,108)  AS MonTotal
,CONVERT(CHAR(8),OutTUEamHour - InTUEamHour + OutTUEpmHour - InTUEpmHour,108)  AS TUETotal
,CONVERT(CHAR(8),OutWEDamHour - InWEDamHour + OutWEDpmHour - InWEDpmHour,108)  AS WEDTotal
,CONVERT(CHAR(8),OutTHURamHour - InTHURamHour + OutTHURpmHour - InTHURpmHour,108)  AS THURTotal
,CONVERT(CHAR(8),OutFRIamHour - InFRIamHour + OutFRIpmHour - InFRIpmHour,108)  AS FRITotal
,CONVERT(CHAR(8),OutMonamHour - InMONamHour + OutMONpmHour - InMONpmHour + OutTUEamHour - InTUEamHour + OutTUEpmHour - InTUEpmHour + OutWEDamHour - InWEDamHour + OutWEDpmHour - InWEDpmHour + OutTHURamHour - InTHURamHour + OutTHURpmHour - InTHURpmHour + OutFRIamHour - InFRIamHour + OutFRIpmHour - InFRIpmHour,108)  AS WEEK
FROM nonexempt
WHERE username = username AND (ID) = (select max(ID) from nonexempt)


In my test...
I am getting a result of 18:45 for the week when I should be seeing 39:45...

Not sure whether you wanted totals by employee or grand total for all employees, so posting both.  Also, there's no need for "username = username" (it's always true), so are you trying to get the maximum id for each user?  Or is the max id the same for all users?  I assumed the max id for each user since that is more code.  If you don't need that, changed the subquery doing the id match below to the one you had original ("ID = (SELECT MAX(ID) FROM nonexempt"):


This will give you a total by employee name (username):

SELECT *, MONTotal + TUETotal + WEDTotal + THUTotal + FRITotal AS [WEEK Total]
FROM (
SELECT username,
CONVERT(CHAR(8), DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour), 108)  AS MONTotal,
CONVERT(CHAR(8), DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour), 108)  AS TUETotal,
CONVERT(CHAR(8), DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour), 108)  AS WEDTotal,
CONVERT(CHAR(8), DATEDIFF(MINUTE, InTHUamHour, OutTHUamHour) + DATEDIFF(MINUTE, InTHUpmHour, OutTHUpmHour), 108)  AS THUTotal,
CONVERT(CHAR(8), DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour), 108)  AS FRITotal
FROM nonexempt
WHERE ID = (SELECT MAX(ID)
     FROM nonexempt ne2
     WHERE nonexempt.username = ne2.username)
) AS nonexemptTotals
ORDER BY username


This will give a grand total for all employees:

SELECT *, MONTotal + TUETotal + WEDTotal + THUTotal + FRITotal AS [WEEK Total]
FROM (
SELECT
CONVERT(CHAR(8), SUM(DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour)), 108)  AS MONTotal,
CONVERT(CHAR(8), SUM(DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour)), 108)  AS TUETotal,
CONVERT(CHAR(8), SUM(DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour)), 108)  AS WEDTotal,
CONVERT(CHAR(8), SUM(DATEDIFF(MINUTE, InTHUamHour, OutTHUamHour) + DATEDIFF(MINUTE, InTHUpmHour, OutTHUpmHour)), 108)  AS THUTotal,
CONVERT(CHAR(8), SUM(DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour)), 108)  AS FRITotal
FROM nonexempt
WHERE ID = (SELECT MAX(ID)
     FROM nonexempt ne2
     WHERE nonexempt.username = ne2.username)
) AS nonexemptGrandTotals

I have tried this and it works fine...alas, without SUMing my week:

SELECT
CONVERT(CHAR(8),OutMonamHour - InMONamHour + OutMONpmHour - InMONpmHour,108)  AS MonTotal
,CONVERT(CHAR(8),OutTUEamHour - InTUEamHour + OutTUEpmHour - InTUEpmHour,108)  AS TUETotal
,CONVERT(CHAR(8),OutWEDamHour - InWEDamHour + OutWEDpmHour - InWEDpmHour,108)  AS WEDTotal
,CONVERT(CHAR(8),OutTHURamHour - InTHURamHour + OutTHURpmHour - InTHURpmHour,108)  AS THURTotal
,CONVERT(CHAR(8),OutFRIamHour - InFRIamHour + OutFRIpmHour - InFRIpmHour,108)  AS FRITotal
,CONVERT(CHAR(8),OutMonamHour - InMONamHour + OutMONpmHour - InMONpmHour + OutTUEamHour - InTUEamHour + OutTUEpmHour - InTUEpmHour + OutWEDamHour - InWEDamHour + OutWEDpmHour - InWEDpmHour + OutTHURamHour - InTHURamHour + OutTHURpmHour - InTHURpmHour + OutFRIamHour - InFRIamHour + OutFRIpmHour - InFRIpmHour,108)  AS WEEK
FROM nonexempt
WHERE username = username AND (ID) = (select max(ID) from nonexempt)


In my test...
I am getting a result of 18:45 for the week when I should be seeing 39:45...

The problem with dates difference as date substruction was in hours overflow your result was 1 day and 39:45.
queries from ScottPletcher solve this problem.

You might format resulting minutes in application or
do it in the query
SELECT
CONVERT(VARCHAR,MONTotal/60) + ':' + CONVERT(CHAR(2),MONTotal%60) MONTotal,
CONVERT(VARCHAR,TUETotal/60) + ':' + CONVERT(CHAR(2),TUETotal%60) TUETotal,
CONVERT(VARCHAR,WEDTotal/60) + ':' + CONVERT(CHAR(2),WEDTotal%60) WEDTotal,
CONVERT(VARCHAR,THUTotal/60) + ':' + CONVERT(CHAR(2),THUTotal%60) THUTotal,
CONVERT(VARCHAR,FRITotal/60) + ':' + CONVERT(CHAR(2),FRITotal%60) FRITotal,
CONVERT(VARCHAR,(MONTotal + TUETotal + WEDTotal + THUTotal + FRITotal )/60)
+ ':' + CONVERT(CHAR(2),(MONTotal + TUETotal + WEDTotal + THUTotal + FRITotal )%60)
WEEK_Total
FROM (
SELECT
SUM(DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour))  AS MONTotal,
SUM(DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour))  AS TUETotal,
SUM(DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour))  AS WEDTotal,
SUM(DATEDIFF(MINUTE, InTHURamHour, OutTHURamHour) + DATEDIFF(MINUTE, InTHURpmHour, OutTHURpmHour))  AS THUTotal,
SUM(DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour))  AS FRITotal
FROM nonexempt) t1

DATEDIFF should be used here instead of (dt1 - dt2)

Doh!  I should have seen that CONVERT to 108 will always truncate days.  Try this:


SELECT CONVERT(CHAR(5), MONMins, 108) AS MONTotal, CONVERT(CHAR(5), TUEMins, 108) AS TUETotal,
     CONVERT(CHAR(5), WEDMins, 108) AS WEDTotal, CONVERT(CHAR(5), THUMins, 108) AS THUTotal,
     CONVERT(CHAR(5), FRIMins, 108) AS FRITotal,
     CAST((MONMins + TUEMins + WEDMins + THUMins + FRIMins) / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0'+CAST((MONMins + TUEMins + WEDMins + THUMins + FRIMins) % 60 AS VARCHAR(2)), 2)
     AS [WEEK Total]
FROM (
SELECT username,
     DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour) AS MONMins,
     DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour) AS TUEMins,
     DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour) AS WEDMins,
     DATEDIFF(MINUTE, InTHUamHour, OutTHUamHour) + DATEDIFF(MINUTE, InTHUpmHour, OutTHUpmHour) AS THUMins,
     DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour) AS FRIMins
FROM nonexempt
WHERE ID = (SELECT MAX(ID) FROM nonexempt)
) AS nonexemptTotals
ORDER BY username

Scott,

Thanks for your help.  It works, only, if there is a NULL for say, Saturday...the week total is not shown...
Any further idea?

Thanks again, JV

Yeah, you can add a check for that:


SELECT CONVERT(CHAR(5), MONMins, 108) AS MONTotal, CONVERT(CHAR(5), TUEMins, 108) AS TUETotal,
    CONVERT(CHAR(5), WEDMins, 108) AS WEDTotal, CONVERT(CHAR(5), THUMins, 108) AS THUTotal,
    CONVERT(CHAR(5), FRIMins, 108) AS FRITotal,
    CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THUMins,0) + ISNULL(FRIMins,0)) / 60 AS VARCHAR(2)) + ':' +
    RIGHT('0'+CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THUMins,0) + ISNULL(FRIMins,0)) % 60 AS VARCHAR(2)), 2)
    AS [WEEK Total]
FROM (
SELECT username,
    DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour) AS MONMins,
    DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour) AS TUEMins,
    DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour) AS WEDMins,
    DATEDIFF(MINUTE, InTHUamHour, OutTHUamHour) + DATEDIFF(MINUTE, InTHUpmHour, OutTHUpmHour) AS THUMins,
    DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour) AS FRIMins
FROM nonexempt
WHERE ID = (SELECT MAX(ID) FROM nonexempt)
) AS nonexemptTotals
ORDER BY username

Ok,

This is accomplishing what i need, only it is not showing values less than a whole number:  (i.e., 1hour 15minutes is showing as 1), but the week is accurately totaled...

SELECT
CONVERT(CHAR(5), MONMins, 108)  / 60 AS MONTotal,
CONVERT(CHAR(5), TUEMins, 108)  / 60 AS TUETotal,
CONVERT(CHAR(5), WEDMins, 108) / 60  AS WEDTotal,
CONVERT(CHAR(5), THURMins, 108) / 60 AS THURTotal,
CONVERT(CHAR(5), FRIMins, 108)  / 60 AS FRITotal,
CONVERT(CHAR(5), SATMins, 108)  / 60 AS SATTotal,
CONVERT(CHAR(5), SUNMins, 108)  / 60 AS SUNTotal,

   CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THURMins,0) + ISNULL(FRIMins,0) + ISNULL(SATMins,0) + ISNULL(SUNMins,0))  / 60 AS VARCHAR(2)) + ':' +
   RIGHT('0'+CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THURMins,0) + ISNULL(FRIMins,0) + ISNULL(SATMins,0) + ISNULL(SUNMins,0)) % 60 AS VARCHAR(2)), 2)
   AS [WEEK Total]

FROM (
SELECT username,
   DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour) AS MONMins,
   DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour) AS TUEMins,
   DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour) AS WEDMins,
   DATEDIFF(MINUTE, InTHURamHour, OutTHURamHour) + DATEDIFF(MINUTE, InTHURpmHour, OutTHURpmHour) AS THURMins,
   DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour) AS FRIMins,
   DATEDIFF(MINUTE, InSATamHour, OutSATamHour) + DATEDIFF(MINUTE, InSATpmHour, OutSATpmHour) AS SATMins,
   DATEDIFF(MINUTE, InSUNamHour, OutSUNamHour) + DATEDIFF(MINUTE, InSUNpmHour, OutSUNpmHour) AS SUNMins
FROM nonexempt
WHERE ID = (SELECT MAX(ID) FROM nonexempt)
) AS nonexemptTotals
ORDER BY username

SELECT
  CAST((ISNULL(MONMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(MONMins,0)) % 60 AS VARCHAR(2)), 2)
  AS MONTotal,
  CAST((ISNULL(TUEMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(TUEMins,0)) % 60 AS VARCHAR(2)), 2)
  AS TUETotal,
  CAST((ISNULL(WEDMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(WEDMins,0)) % 60 AS VARCHAR(2)), 2)
  AS WEDTotal,
  CAST((ISNULL(THURMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(THURMins,0)) % 60 AS VARCHAR(2)), 2)
  AS THURTotal,
  CAST((ISNULL(FRIMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(FRIMins,0)) % 60 AS VARCHAR(2)), 2)
  AS FRITotal,
  CAST((ISNULL(SATMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(SATMins,0)) % 60 AS VARCHAR(2)), 2)
  AS SATTotal,
  CAST((ISNULL(SUNMins,0)) / 60 AS VARCHAR(2)) + ':' +
  RIGHT('0'+CAST((ISNULL(SUNMins,0)) % 60 AS VARCHAR(2)), 2)
  AS SUNTotal,
  RIGHT('0'+CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THURMins,0) + ISNULL(FRIMins,0) + ISNULL(SATMins,0) + ISNULL(SUNMins,0))  / 60 AS VARCHAR(2)),2) + ':' +
  RIGHT('0'+CAST((ISNULL(MONMins,0) + ISNULL(TUEMins,0) + ISNULL(WEDMins,0) + ISNULL(THURMins,0) + ISNULL(FRIMins,0) + ISNULL(SATMins,0) + ISNULL(SUNMins,0)) % 60 AS VARCHAR(2)), 2)
  AS [WEEK Total]

FROM (
SELECT username,
  DATEDIFF(MINUTE, InMONamHour, OutMONamHour) + DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour) AS MONMins,
  DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour) + DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour) AS TUEMins,
  DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour) + DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour) AS WEDMins,
  DATEDIFF(MINUTE, InTHURamHour, OutTHURamHour) + DATEDIFF(MINUTE, InTHURpmHour, OutTHURpmHour) AS THURMins,
  DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour) + DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour) AS FRIMins,
  DATEDIFF(MINUTE, InSATamHour, OutSATamHour) + DATEDIFF(MINUTE, InSATpmHour, OutSATpmHour) AS SATMins,
  DATEDIFF(MINUTE, InSUNamHour, OutSUNamHour) + DATEDIFF(MINUTE, InSUNpmHour, OutSUNpmHour) AS SUNMins
FROM nonexempt
WHERE ID = (SELECT MAX(ID) FROM nonexempt)
) AS nonexemptTotals
ORDER BY username

BRILLIANT!!

Works like a charm!

Thanks again for your hard work on this...

JV