title: How to sum total hrs in cte
body: <p>I want to sum worked hrs 2+3+4=9.Below is query i tried but sum not work </p>
  
  <blockquote>
    <p>MyQuery:</p>
  </blockquote>
  
  <pre><code>;with cte as
  (
  Select t.emp_reader_id as empId, 
  cast(max(dt) as date) as Belongs_to,
  Cast(dateadd(ss,Datediff(second,max(iif(EVENTID = 0, DT, null)),max(iif(EVENTID = 1, DT, null))),0)As Time(0)) as Worked,
  case 
  when LOWER(DATENAME(dw, t.att_date))='friday' 
  then 
  Cast(dateadd(ss,Datediff(second,max(iif(EVENTID = 0, DT, null)),max(iif(EVENTID = 1, DT, null))),0)As Time(0))
  else '00:00'
  end  as Friday_Worked,
  max(iif(EVENTID = 0, dt, null)) as EntryTime, 
  max(iif(EVENTID = 1, dt, null)) as ExitTime
  from (  
     select
     Trnevents.emp_reader_id,Trnevents.EVENTID,Trnevents.DT,Trnevents.belongs_to,Daily_attendance_data.att_date,
      grp = sum(iif(Trnevents.EVENTID = 0, 1, 0) ) over (partition by Trnevents.emp_reader_id order by Trnevents.DT)
          --*, grp = sum(iif(Trnevents.EVENTID = 0, 1, 0) ) over (partition by Trnevents.emp_reader_id order by Trnevents.DT)
      from
          Trnevents 
          inner join Daily_attendance_data 
          on Daily_attendance_data.emp_reader_id=Trnevents.emp_reader_id --and dd.att_date=tt.Belongs_to
          where 
          Trnevents.emp_reader_id=9999 and 
          (CONVERT(VARCHAR(26),Daily_attendance_data.att_date, 23) &gt;=CONVERT(VARCHAR(26), '2018-10-25', 23)
           and CONVERT(VARCHAR(26), Daily_attendance_data.att_date, 23) &lt;=CONVERT(VARCHAR(26), '2018-10-25', 23))  and Trnevents.DT between Daily_attendance_data.in_time and Daily_attendance_data.out_time
  ) t 
    group by t.emp_reader_id,t.grp,t.att_date
    --order by t.grp asc
   ) 
   select empid,B.emp_name,Belongs_to,Worked,Friday_Worked,Entrytime,ExitTime,(DATEPART(HOUR,[worked])) as 'TotalHRS'
    from cte
    inner join employee B on cte.empid=B.emp_reader_id
   group by empid,Belongs_to,worked,friday_worked,entrytime,exittime,B.emp_name
   order by 1
  </code></pre>
  
  <p>Output i get is ok but i need extra one column for sum total hrs for that particular id or date
      Output:</p>
  
  <pre><code>empid   emp_name Belongs_to Worked  Friday_Worked   Entrytime   ExitTime        TotalHRS
  9999    Test    2018-10-25  02:00:00    00:00:00    2018-10-25 18:00:00.000 2018-10-25 20:00:00.000 2
  9999    Test    2018-10-26  03:00:00    00:00:00    2018-10-25 22:00:00.000 2018-10-26 01:00:00.000 3
  9999    Test    2018-10-26  04:00:00    00:00:00    2018-10-26 02:00:00.000 2018-10-26 06:00:00.000 4
  </code></pre>