I want to SUM the value of time on an SQL server

Question

SELECT DISTINCT (DATEDIFF(DAY, '2014-06-01', '2014-06-30') + 1) - DATEDIFF(WEEK, '2014-06-01', '2014-06-30') * 2 - (CASE WHEN DATEPART(WEEKDAY, '2014-06-01') = 5 THEN 1 ELSE 0 END) - (CASE WHEN DATEPART(WEEKDAY, '2014-06-30') = 6 THEN 1 ELSE 0 END) AS TotalWorkingDays, COUNT(DISTINCT DATEADD(d, 0,DATEDIFF(d, 0, CHECKINOUT.CHECKTIME))) AS workingdays,
 USERINFO.NAME,
case when  '08:00:00.000' > cast(convert(char(8),min(checktime),8) as datetime) THEN NULL
ELSE 
cast(dateadd(minute,datediff(minute,'08:00:00.000',min(checktime)),'00:00')AS dateTIME) END as LATEIN ,
case when '17:00:00.000' < cast(convert(char(8),max(checktime),8) as datetime) THEN NULL 
ELSE
cast(dateadd(minute,datediff(minute,max(checktime),'17:00:00.000'),'00:00')AS dateTIME)END as LATEOUT     
FROM   USERINFO left JOIN CHECKINOUT ON CHECKINOUT.USERID = USERINFO.USERID 
left join departments on deptid = userinfo.DEFAULTDEPTID
WHERE(DEPARTMENTS.DEPTNAME = 'GEN/SUP-TBL') AND (CHECKINOUT.CHECKTIME >='2014-06-01') AND (CHECKINOUT.CHECKTIME <='2014-06-30') and name='Gokul Gopalakrishnan'
GROUP BY USERINFO.NAME,checktime

this is my query and it gives below output:

TotalWorkingDays    workingdays NAMER   LATEIN                          LATEOUT
22                  1           abc      NULL                   1785-07-17 09:24:00.000
22                  1           abc      NULL                   1785-07-18 00:15:00.000
22                  1           abc      NULL                   1785-07-31 00:30:00.000
22                  1           abc    2014-06-01 00:39:00.000  1785-08-02 01:00:00.000
22                  1           abc    2014-06-01 09:14:00.000     NULL

But now i want the output like below:

TotalWorkingDays    workingdays NAME    TOTAL LATEIN        TOTAL LATEOUT
22                    5         abc     09:53:00.000       10:69:00.000

Is there any way to do this????

Is there any way to do this???? *YES!* it involves string manipulation to parse out each segment around the ":" then summing each together and then rebuilding a new string output. you know the length of each value is consistent so cast to string, part it out into the various elements add those results then build your output string. But why on Earth... someone would want to do this... — xQbert, Jul 02 '14 at 17:26
It looks like a duplicate question: see this http://stackoverflow.com/questions/13991440/sum-of-datetime-difference-in-sql-hh-mm Regards, — Alexander Bell, Jul 02 '14 at 17:29
...I don't understand why you would want to do this. I'm assuming the goal is to record how late a person arrives/leaves (call center software?). If thats the case...why hold the date and time in one field? Why not break out the date and have a 'minutes late' column instead of trying to cram it all into a date field? — Twelfth, Jul 02 '14 at 17:55
@Twelfth...ya you are right..but i dont have authority to change the datatype of it... — rushank shah, Jul 02 '14 at 18:04
@AlexBell link looks to provide the answer. Longer explanations for working with durations/timespans can be found for example: http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server — Allan S. Hansen, Jul 02 '14 at 18:50

xQbert · Accepted Answer · 2014-07-02T18:39:24.563

FULL WORKUP:

We first convert the time to a string format in the desired output. using convert(nvarchar(50),time,121)
We then substring out each time set so it can be summed together.
Prior to summing we have to cast the string back to numeric value
Now that we have the sum we have to ensure proper 0 leading padding right('00+convert...)

Resulting in: .

Select 
  TotalWorkingDays, 
  sum(workingDays) as WorkingDays,
  Namer,  
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),12,2) as Numeric(2,0)))),2)+ ':'+
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),15,2) as Numeric(2,0)))),2)+ ':'+      
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),18,2) as Numeric(2,0)))),2)+ ':'+     
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),21,3) as Numeric(3,0)))),3) as LateIn,
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),12,2) as Numeric(2,0)))),2)+ ':'+
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),15,2) as Numeric(2,0)))),2)+ ':'+      
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),18,2) as Numeric(2,0)))),2)+ ':'+     
  right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),21,3) as Numeric(3,0)))),3) as LateOut 
from blue
Group by TotalWorkingDays, namer

using this example http://sqlfiddle.com/#!3/81c0c/2/0

This has a significant issue however

Say a summed hour, minute or second exceeds 2 digits. Your 53 is instead... 100... what should happen? Presently this only looks at the two right most characters what should happen?

Your output can't be datetime datatype as 69 is not a valid minute... as in 10:69:00.000, thus the output; So either the output is varchar, or your expected result is incorrect.

Now integration to your existing query:

With CTE as (
SELECT DISTINCT (DATEDIFF(DAY, '2014-06-01', '2014-06-30') + 1) - DATEDIFF(WEEK, '2014-06-01', '2014-06-30') * 2 - (CASE WHEN DATEPART(WEEKDAY, '2014-06-01') = 5 THEN 1 ELSE 0 END) - (CASE WHEN DATEPART(WEEKDAY, '2014-06-30') = 6 THEN 1 ELSE 0 END) AS TotalWorkingDays, COUNT(DISTINCT DATEADD(d, 0,DATEDIFF(d, 0, CHECKINOUT.CHECKTIME))) AS workingdays,
 USERINFO.NAME,
case when  '08:00:00.000' > cast(convert(char(8),min(checktime),8) as datetime) THEN NULL
ELSE 
cast(dateadd(minute,datediff(minute,'08:00:00.000',min(checktime)),'00:00')AS dateTIME) END as LATEIN ,
case when '17:00:00.000' < cast(convert(char(8),max(checktime),8) as datetime) THEN NULL 
ELSE
cast(dateadd(minute,datediff(minute,max(checktime),'17:00:00.000'),'00:00')AS dateTIME)END as LATEOUT     
FROM   USERINFO left JOIN CHECKINOUT ON CHECKINOUT.USERID = USERINFO.USERID 
left join departments on deptid = userinfo.DEFAULTDEPTID
WHERE(DEPARTMENTS.DEPTNAME = 'GEN/SUP-TBL') AND (CHECKINOUT.CHECKTIME >='2014-06-01') AND (CHECKINOUT.CHECKTIME <='2014-06-30') and name='Gokul Gopalakrishnan'
GROUP BY USERINFO.NAME,checktime)
  Select 
      TotalWorkingDays, 
      sum(workingDays) as WorkingDays,
      Namer,  
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),12,2) as Numeric(2,0)))),2)+ ':'+
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),15,2) as Numeric(2,0)))),2)+ ':'+      
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),18,2) as Numeric(2,0)))),2)+ ':'+     
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),21,3) as Numeric(3,0)))),3) as LateIn,
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),12,2) as Numeric(2,0)))),2)+ ':'+
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),15,2) as Numeric(2,0)))),2)+ ':'+      
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),18,2) as Numeric(2,0)))),2)+ ':'+     
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),21,3) as Numeric(3,0)))),3) as LateOut 
    from CTE
    Group by TotalWorkingDays, namer

OR the old fassion way...

 Select 
      TotalWorkingDays, 
      sum(workingDays) as WorkingDays,
      Namer,  
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),12,2) as Numeric(2,0)))),2)+ ':'+
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),15,2) as Numeric(2,0)))),2)+ ':'+      
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),18,2) as Numeric(2,0)))),2)+ ':'+     
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateIn,121),21,3) as Numeric(3,0)))),3) as LateIn,
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),12,2) as Numeric(2,0)))),2)+ ':'+
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),15,2) as Numeric(2,0)))),2)+ ':'+      
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),18,2) as Numeric(2,0)))),2)+ ':'+     
      right('00'+convert(varchar,sum(cast(substring(convert(nvarchar(50),LateOut,121),21,3) as Numeric(3,0)))),3) as LateOut 
    from (
    SELECT DISTINCT (DATEDIFF(DAY, '2014-06-01', '2014-06-30') + 1) - DATEDIFF(WEEK, '2014-06-01', '2014-06-30') * 2 - (CASE WHEN DATEPART(WEEKDAY, '2014-06-01') = 5 THEN 1 ELSE 0 END) - (CASE WHEN DATEPART(WEEKDAY, '2014-06-30') = 6 THEN 1 ELSE 0 END) AS TotalWorkingDays, COUNT(DISTINCT DATEADD(d, 0,DATEDIFF(d, 0, CHECKINOUT.CHECKTIME))) AS workingdays,
     USERINFO.NAME,
    case when  '08:00:00.000' > cast(convert(char(8),min(checktime),8) as datetime) THEN NULL
    ELSE 
    cast(dateadd(minute,datediff(minute,'08:00:00.000',min(checktime)),'00:00')AS dateTIME) END as LATEIN ,
    case when '17:00:00.000' < cast(convert(char(8),max(checktime),8) as datetime) THEN NULL 
    ELSE
    cast(dateadd(minute,datediff(minute,max(checktime),'17:00:00.000'),'00:00')AS dateTIME)END as LATEOUT     
    FROM   USERINFO left JOIN CHECKINOUT ON CHECKINOUT.USERID = USERINFO.USERID 
    left join departments on deptid = userinfo.DEFAULTDEPTID
    WHERE(DEPARTMENTS.DEPTNAME = 'GEN/SUP-TBL') AND (CHECKINOUT.CHECKTIME >='2014-06-01') AND (CHECKINOUT.CHECKTIME <='2014-06-30') and name='Gokul Gopalakrishnan'
    GROUP BY USERINFO.NAME,checktime) blue
    Group by TotalWorkingDays, namer

it gives following error: Argument data type datetime is invalid for argument 1 of substring function. — rushank shah, Jul 02 '14 at 17:56
still it give same error: Argument data type datetime is invalid for argument 1 of substring function. — rushank shah, Jul 02 '14 at 18:01
still give same error...ya you have used varchar but there is datetime data type... — rushank shah, Jul 02 '14 at 18:09
use `convert(nvarchar(MAX), LateIn, 121)` 121 sourced from: http://stackoverflow.com/questions/74385/how-to-convert-datetime-to-varchar — xQbert, Jul 02 '14 at 18:11
ya your fiddle is right but we have problem of datetime datatype.... hope will get proper way to do this... — rushank shah, Jul 02 '14 at 18:22
yup ....great work sir ...but in my query i have used min(checktime) and max(checktime) it is not working for that....will you please help me out... — rushank shah, Jul 02 '14 at 18:35
Thank you very much sir....great work...only workingdays is not work...else are fine..:-) — rushank shah, Jul 02 '14 at 18:50
What's not working? are you after a sum, a count? did I name the field wrong? — xQbert, Jul 02 '14 at 18:51
old fashion way code is working perfect ....Awesome work sir.... Thank you very much ...:-) — rushank shah, Jul 02 '14 at 18:55
*sigh* Your output can't be datetime datatype as 69 is not a valid minute... as in 10:69:00.000, thus the output; So either the output is varchar, or your expected result is incorrect. This means a different path entirely. PLEASE validate expected results and update before proceeding. — xQbert, Jul 04 '14 at 11:58

I want to SUM the value of time on an SQL server

1 Answers1