I cannot subtract values

Transact-SQL
1

Hello,

I want to use the two values in the select field. I want to subtract the Pauzetime from the N@Worktime value.

I tried

but I get the reaction:
Msg 8117, Level 16, State 1, Procedure SIP_rpt_R9002, Line 52
Operand data type nvarchar is invalid for subtract operator.

How to solve this issue?


BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

Declare
@Pauze Varchar,
@Realstarttime Int,
@Realendtime Int,
@Worktime Int



select TR.EmpId
      ,TR.StartTime as starttimes
      ,TR.StartDate
      ,DATEPART(WEEK,TR.StartDate) as weeknr
      ,DATEPART(weekday,TR.startDate) as weekday
      ,convert(varchar,DATeADD(s,TR.StartTime,0),108) as Starttime
      ,convert(varchar,DATeADD(s,(case when TR.StartTime<=WH.MaxStartTime 
		and TR.StartTime>=WH.MinStartTime 
		then WH.StartTime else TR.StartTime end),0),108) as N'@Realstarttime'
      ,TR.EndTime as endtimes
      ,convert(varchar,DATeADD(s,TR.EndTime,0),108) as Endtime
      ,convert(varchar,DATeADD(s,(case when (TR.EndTime<=WH.MaxEndTime 
		and TR.EndTime>=WH.MinEndTime) 
		then WH.EndTime else TR.EndTime end),0),108) as N'@Realendtime'
      ,H.Description
      ,E.FirstName
      ,E.Name
	  ,SUM((case when TR.EndTime<=WH.MaxEndTime 
		and TR.EndTime>=WH.MinEndTime 
		then WH.EndTime else TR.EndTime end)-(case when TR.StartTime<=WH.MaxStartTime 
		and TR.StartTime>=WH.MinStartTime 
		then WH.StartTime else TR.StartTime end)) as N'@worktime'
      ,convert(int,isnull(sum( case when WHL.EndTime>TR.EndTime then TR.Endtime else WHL.EndTime end-
                   case when WHL.StartTime<TR.StartTime then TR.StartTime else WHL.StartTime end
                   ),0))as N'@Pauze'
      ,convert(varchar,DATeADD(s,(sum(case when WHL.EndTime>TR.EndTime then TR.Endtime else WHL.EndTime end-
                   case when WHL.StartTime<TR.StartTime then TR.StartTime else WHL.StartTime end
                   )),0),108) as Pauzetime
     ,sum(N'@worktime'-N'@Pauze') as totalworktime
 
                 
  from T_Hour as H, T_WorkingHour as WH, T_Employee as E, T_TimeRegistration as TR 
       left outer join T_WorkingHourLines as WHL
                    on WHL.StartTime<TR.EndTime
                   and WHL.EndTime>TR.StartTime
                   and WHL.WorkingHourTypeCode='PZ' 
                   and WHL.WorkingHourCode=TR.WorkingHourCode
        where H.HourCode=TR.HourCode and E.EmpId=TR.EmpId and WH.WorkingHourCode=TR.WorkingHourCode
group by 
        TR.EmpId
		,TR.StartDate
		,TR.StartTime
        ,TR.EndTime
        ,H.Description
        ,E.Name
        ,E.FirstName
        ,WH.StartTime
        ,WH.MaxStartTime
		,WH.MinStartTime
		,WH.EndTime
        ,WH.MaxEndTime
		,WH.MinEndTime
return 0
2

You could repeat the whole SUM statement for you "Worktime" and "Pauze" in the SUM for totalworktime, or you could use a CTE or APPLY statement to make that available elsewhere (with that name). Personal I find those routes more difficult to understand ...

My approach is to wrap the query and make it into a sub query

SELECT *
       ,[@worktime] - [@Pauze] as totalworktime
FROM
(
       select TR.EmpId
              ,TR.StartTime as starttimes
              ... rest of your original query ...
              ... BUT LEAVE OUT "sum(N'@worktime'-N'@Pauze') as totalworktime" ...
       group by 
		TR.EmpId
		... ... ...
		,WH.MinEndTime
) AS X
3

Thank Kristen,

it worked out fine!!