?
Solved

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

Posted on 2003-03-03
19
Medium Priority
?
204 Views
Last Modified: 2012-06-27
THIS IS A TIME SLIP SCENARIO...using ASP and SQL SERVER 2000

For an employee time slip system I have the following fields in a table called NONEXMPT:

InMONamHour
InTUEamHour
InWEDamHour
InTHURamHour
InFRIamHour
InSATamHour
InSUNamHour
OutMONamHour
OutTUEamHour
OutTHURamHour
OutFRIamHour
OutSATamHour
OutSUNamHour
InMONpmHour
InTUEpmHour
InWEDpmHour
InTHURpmHour
InFRIpmHour
InSATpmHour
InSUNpmHour
OutMONpmHour
OutTUEpmHour
OutWEDpmHour
OutTHURpmHour
OutFRIpmHour
OutSATpmHour
OutSUNpmHour

What I would like to do is SUM the time for morning and then the afternoon, and then SUM those SUMs for a total for the week.

SOMETHING LIKE...
OutMONamHour - InMONamHour = MONam
OutMONpmHour - InMONpmHour = MONpm
MONam + MONpm = MON
MON + TUE + WED (and so on to end with) = WEEK TOTAL


THIS IS WHAT I HAVE SO FAR...
SELECT (InMONpmHour - OutMONamHour) AS MON FROM nonexempt WHERE username = username AND (ID) = (select max(ID) from nonexempt)

Any help is appreciated, JV
0
Comment
Question by:jvescio
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
  • +2
19 Comments
 

Expert Comment

by:aramxxii
ID: 8062484
I would suggest that you restructure your table into

Username
WorkDate
TimeIn
TimeOut

This way daily and weekly subtotals can be calculated easily.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8063370
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.
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8063404
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.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:jvescio
ID: 8063938
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?
0
 

Expert Comment

by:cherrypie
ID: 8064704
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
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8064844
How did you set length for datetime column? It has no such property.
Could you give me an example of nMONamHour value?
0
 

Author Comment

by:jvescio
ID: 8064912
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
0
 

Author Comment

by:jvescio
ID: 8065011
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...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8065391
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
0
 

Author Comment

by:jvescio
ID: 8065533
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...
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8065572
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
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8065589
DATEDIFF should be used here instead of (dt1 - dt2)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8065635
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
0
 

Author Comment

by:jvescio
ID: 8067036
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
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 8067152
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
0
 

Author Comment

by:jvescio
ID: 8071671
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
0
 
LVL 15

Expert Comment

by:OMC2000
ID: 8071910
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 8072184
The daily CONVERT is not working like I thought it would.  Sorry about another mix-up.  It is best to get rid of NULLs in the inner query to simplify the outer query.  Please try this:


SELECT
CAST(MONMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(MONMins % 60 AS VARCHAR(2)), 2) AS MONTotal,
CAST(TUEMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(TUEMins % 60 AS VARCHAR(2)), 2) AS TUETotal,
CAST(WEDMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(WEDMins % 60 AS VARCHAR(2)), 2) AS WEDTotal,
CAST(THUMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(THUMins % 60 AS VARCHAR(2)), 2) AS THUTotal,
CAST(FRIMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(FRIMins % 60 AS VARCHAR(2)), 2) AS FRITotal,
CAST(SATMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(SATMins % 60 AS VARCHAR(2)), 2) AS SATTotal,
CAST(SUNMins / 60 AS VARCHAR(2)) + ':' +
     RIGHT('0' + CAST(SUNMins % 60 AS VARCHAR(2)), 2) AS SUNTotal,
CAST((MONMins + TUEMins + WEDMins + THUMins + FRIMins + SATMins + SUNMins) / 60 AS VARCHAR(2)) +
     ':' + RIGHT('0' + CAST((MONMins + TUEMins + WEDMins + THUMins + FRIMins + SATMins + SUNMins) % 60 AS VARCHAR(2)), 2)
     AS [WEEK Total]
FROM (
SELECT username,
   ISNULL(DATEDIFF(MINUTE, InMONamHour, OutMONamHour), 0) + ISNULL(DATEDIFF(MINUTE, InMONpmHour, OutMONpmHour), 0) AS MONMins,
   ISNULL(DATEDIFF(MINUTE, InTUEamHour, OutTUEamHour), 0) + ISNULL(DATEDIFF(MINUTE, InTUEpmHour, OutTUEpmHour), 0) AS TUEMins,
   ISNULL(DATEDIFF(MINUTE, InWEDamHour, OutWEDamHour), 0) + ISNULL(DATEDIFF(MINUTE, InWEDpmHour, OutWEDpmHour), 0) AS WEDMins,
   ISNULL(DATEDIFF(MINUTE, InTHUamHour, OutTHUamHour), 0) + ISNULL(DATEDIFF(MINUTE, InTHUpmHour, OutTHUpmHour), 0) AS THUMins,
   ISNULL(DATEDIFF(MINUTE, InFRIamHour, OutFRIamHour), 0) + ISNULL(DATEDIFF(MINUTE, InFRIpmHour, OutFRIpmHour), 0) AS FRIMins,
   ISNULL(DATEDIFF(MINUTE, InSATamHour, OutSATamHour), 0) + ISNULL(DATEDIFF(MINUTE, InSATpmHour, OutSATpmHour), 0) AS SATMins,
   ISNULL(DATEDIFF(MINUTE, InSUNamHour, OutSUNamHour), 0) + ISNULL(DATEDIFF(MINUTE, InSUNpmHour, OutSUNpmHour), 0) AS SUNMins
FROM nonexempt
WHERE ID = (SELECT MAX(ID) FROM nonexempt)
) AS nonexemptTotals
ORDER BY username
0
 

Author Comment

by:jvescio
ID: 8074997
BRILLIANT!!

Works like a charm!

Thanks again for your hard work on this...

JV
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question