Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

calculate Excess/Short and show in one row Expand / Collapse
Author
Message
Posted Wednesday, June 12, 2013 1:52 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 12:05 AM
Points: 99, Visits: 304
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
Post #1462489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse