Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2003-03-03
19
Medium Priority
?
205 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 70

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 70

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 70

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 70

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

572 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