immad
Programmer
- May 31, 2013
- 7
this is my data
-------------------------row 1--------------------------row 2
date-------------2013-01-04 00:00:00.000-------2013-01-04 00:00:00.000
eid--------------26446-----------------------------26446
timein-----------2013-06-11 09:44:00.000-----2013-06-11 13:56:00.000
timeout---------2013-06-11 13:20:00.000-----2013-06-11 18:26:00.000
spend-----------08:06:00------------------------NULL
excess short----5:24-----------------------------4:30
i want to add excess short column and show in one row
this is what i want
-------------------------row 1--------------------------row 2
date-------------2013-01-04 00:00:00.000---------2013-01-04 00:00:00.000
eid---------------26446--------------------------26446
timein-------------2013-06-11 09:44:00.000-------2013-06-11 13:56:00.000
timeout------------2013-06-11 13:20:00.000-------2013-06-11 13:56:00.000
spend---------------08:06:00---------------------NULL
excess short------------00:54--------------------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
please implement on this query
help me out please
-------------------------row 1--------------------------row 2
date-------------2013-01-04 00:00:00.000-------2013-01-04 00:00:00.000
eid--------------26446-----------------------------26446
timein-----------2013-06-11 09:44:00.000-----2013-06-11 13:56:00.000
timeout---------2013-06-11 13:20:00.000-----2013-06-11 18:26:00.000
spend-----------08:06:00------------------------NULL
excess short----5:24-----------------------------4:30
i want to add excess short column and show in one row
this is what i want
-------------------------row 1--------------------------row 2
date-------------2013-01-04 00:00:00.000---------2013-01-04 00:00:00.000
eid---------------26446--------------------------26446
timein-------------2013-06-11 09:44:00.000-------2013-06-11 13:56:00.000
timeout------------2013-06-11 13:20:00.000-------2013-06-11 13:56:00.000
spend---------------08:06:00---------------------NULL
excess short------------00:54--------------------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
please implement on this query
help me out please