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
Medium Priority
205 Views
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
Question by:jvescio
• 7
• 6
• 4
• +2

Expert Comment

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

WorkDate
TimeIn
TimeOut

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

LVL 15

Expert Comment

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
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

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

Author Comment

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

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

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

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?

0

Author Comment

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

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

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 (
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
) AS nonexemptTotals

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
) AS nonexemptGrandTotals
0

Author Comment

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

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

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

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

LVL 70

Expert Comment

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 (
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
0

Author Comment

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

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 (
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
0

Author Comment

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 (
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
0

LVL 15

Expert Comment

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 (
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
0

LVL 70

Accepted Solution

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 (
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
0

Author Comment

ID: 8074997
BRILLIANT!!

Works like a charm!

Thanks again for your hard work on this...

JV
0

## Featured Post

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
Course of the Month10 days, 20 hours left to enroll