how to sum time in sql server 2008?

1
votes

How to sum the time columns in sql server

I want to sum the monday, tuesday,wednesday, thursday,friday and saturday timings and update into Tot_hrs I have update logic I am unable to sum the hrs from Monday to Satday

CREATE TABLE WEEKLY_ATTN(
[SYSID] [int] IDENTITY(1,1) NOT NULL,
[EMPLOYEE_SYSID] [int] NULL,
[Mon_day] [varchar](10) NULL,
[Tue_day] [varchar](10) NULL,
[Wed_day] [varchar](10) NULL,
[Thu_day] [varchar](10) NULL,
[Fri_day] [varchar](10) NULL,
[Sat_day] [varchar](10) NULL,
[Tot_Hrs] [varchar](20) NULL

);

INSERT INTO WEEKLY_ATTN (employee_sysid,mon_day,tue_day,wed_day,thu_day,fri_day,sat_day)
values(41,'11:01','11:57','11:02','10:19','09:26',null);

Below is the query which I have used

select mon_day,Tue_day,wed_day,thu_day,fri_Day,sat_day,
cast(dateadd(s,
isnull(datediff(s, 0, mon_day), 0)+ 
isnull(datediff(s, 0, Tue_day), 0)+ 
isnull(datediff(s, 0, wed_day), 0)+
isnull(datediff(s, 0, Thu_day), 0)+
isnull(datediff(s, 0, fri_day), 0)+
isnull(datediff(s, 0, sat_day), 0)
,0) as time(0)) as Tot_hrs
from weekly_attn where employee_sysid=41; 

mon_day Tue_day wed_day thu_day fri_Day  sat_day tot_hrs
---------------------------------------------------------
11:01    11:57  11:02   10:19   09:26    NULL    05:45:00

however total hours is 53:45 hours can any one correct me where it is wrong. Any help?

2
sql-serversql-server-2008-r2
I got the answer by the following way – user3012724
Hi. Which one is the 'following way' ? – BAdmin
I got the answer by the following way set @V_TOTAL_HRS=(select isnull(datediff(s, 0, mon_day), 0)+ isnull(datediff(s, 0, Tue_day), 0)+ isnull(datediff(s, 0, wed_day), 0)+ isnull(datediff(s, 0, Thu_day), 0)+ isnull(datediff(s, 0, fri_day), 0) from weekly_attn where employee_sysid=@V_EMPID) --select @V_TOTAL_HRS=cast(@V_TOTAL_HRS/3600 as varchar)+':'+cast((@V_TOTAL_HRS%3600)/60 as varchar) -- 53:45 – user3012724
Good work bro.. Keep it up :) – BAdmin

2 Answers

0
votes

varchar should be time.

select *,CONVERT(varchar, DATEADD(ms,Tot_hrs * 1000, 0),114) from 
(select mon_day,Tue_day,wed_day,thu_day,fri_Day,sat_day,
isnull(cast(datediff(s, 0,cast( mon_day as time)) as float),0)+ 
isnull(cast(datediff(s, 0,cast(  Tue_day as time))as float),0)+ 
isnull(cast(datediff(s, 0,cast(  wed_day as time))as float),0)+
isnull(cast(datediff(s, 0,cast(  Thu_day as time))as float),0)+
isnull(cast(datediff(s, 0,cast( fri_day as time))as float),0)+
isnull(cast(datediff(s, 0,cast( sat_day as time)) as float),0)
as Tot_hrs
from @weekly_attn where employee_sysid=41)tbl
0
votes

Following is way a way to sum you hours

select 
CAST(
    CAST(SUBSTRING(isnull(mon_day,0),1,2) as int)+CAST(SUBSTRING(isnull(Tue_day,0),1,2) as int)+
    CAST(SUBSTRING(isnull(wed_day,0),1,2) as int)+CAST(SUBSTRING(isnull(Thu_day,0),1,2) as int)+
    CAST(SUBSTRING(isnull(fri_day,0),1,2) as int)+CAST(SUBSTRING(isnull(sat_day,0),1,2) as int) 
    +   
    (
    CAST(SUBSTRING(isnull(mon_day,0),4,5) as int)+CAST(SUBSTRING(isnull(Tue_day,0),4,5) as int)+
    CAST(SUBSTRING(isnull(wed_day,0),4,5) as int)+CAST(SUBSTRING(isnull(Thu_day,0),4,5) as int)+
    CAST(SUBSTRING(isnull(fri_day,0),4,5) as int)+CAST(SUBSTRING(isnull(sat_day,0),4,5) as int) 
    )/60

AS varchar(50))
+':'+
CAST(
    (
    CAST(SUBSTRING(isnull(mon_day,0),4,5) as int)+CAST(SUBSTRING(isnull(Tue_day,0),4,5) as int)+
    CAST(SUBSTRING(isnull(wed_day,0),4,5) as int)+CAST(SUBSTRING(isnull(Thu_day,0),4,5) as int)+
    CAST(SUBSTRING(isnull(fri_day,0),4,5) as int)+CAST(SUBSTRING(isnull(sat_day,0),4,5) as int) 
    )%60
AS varchar(50))
from weekly_attn

output: 53:45