calculate Excess/Short and show in one row

• immaduddinahmed

  Ten Centuries

  Points: 1375

  More actions

  June 12, 2013 at 1:52 am

  #146178

  this is my data

  date---------------------------eid-------------timein-------------------------timeout--------------spend-----excess short

  2013-01-04 00:00:00.000--26446--2013-06-11 09:44:00.000--2013-06-11 13:20:00.000--08:06:00------5:24

  2013-01-04 00:00:00.000--26446--2013-06-11 13:56:00.000--2013-06-11 18:26:00.000--NULL------------4:30

  i want to add excess short column and show in one row

  this is what i want

  date---------------------------eid-------------timein-------------------------timeout--------------spend-----excess short

  2013-01-04 00:00:00.000--26446--2013-06-11 09:44:00.000--2013-06-11 13:20:00.000--08:06:00-------00:54

  2013-01-04 00:00:00.000--26446--2013-06-11 13:56:00.000--2013-06-11 18:26:00.000--NULL------------NULL

  i calculate excess short time from timein and timeout

  this is my query

  select

  [date],

  min([Timein]) as First_Record,

  sum(DATEDIFF(ss, [Timein], [Timeout])) as Time_In_Seconds

  into #temp1 from ATTEND_LOG

  where eid=26153

  group by [date]

  GO

  select

  t.[date],

  t.eid,

  t.[Timein] timein,

  t.[Timeout]timeout,

  CONVERT(VARCHAR(8), DATEADD(ss, Time_In_Seconds, 0), 108) AS SpendTime,

  CAST (ABS( convert(varchar(10),540,108) - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) / 60 as varchar ) + ':' +-- Hours ( + ':' separator )

  CAST (ABS (convert(varchar(10),540,108) - DATEDIFF (MINUTE, t.Timein, t.timeout ) ) % 60 as varchar ) as excesshorttime

  FROM ATTEND_LOG t

  left join #temp1 t2 on t.[date]=t2.[date] and t.[Timein] = t2.First_Record

  where eid=26446

  help me out please

  immad