Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

calculate excess short and show in one column

Status
Not open for further replies.

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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top