June 12, 2013 at 1:52 am
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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply