June 13, 2010 at 9:37 pm
Hi. I have a database which collects time records from a Attendance Time Log Machine. It captures all the datetimes for specific employees. I am using a T-Sql command to capture all the records for a given employee. However, I could not ignore time records within a 01 minute time frame. I just want to ignore any records within a minute time frame for that specific employee.
Pls help
Thanks in advance
June 13, 2010 at 11:27 pm
Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 14, 2010 at 1:47 am
For example, I have a table with a schema
CREATE TABLE #Test {
EmployeeID Int,
LogTime DateTime
}
INSERT INTO #Test
(1001, '01/01/2010 10:22:36'
1002, '01/01/2010 10:24:37'
1003, '01/01/2010 10:24:38'
1001, '01/01/2010 10:24:39'
1001, '01/01/2010 10:24:40'
1002, '01/01/2010 10:24:45'
1003, '01/01/2010 10:26:45'
)
While selecting data from the above table with the sample records, I want the output to appear like,
Output
1001, '01/01/2010 10:22:36'
1002, '01/01/2010 10:24:37'
1003, '01/01/2010 10:24:38'
1003, '01/01/2010 10:26:45'
Thus, rejecting all the duplicate times within 01 Minute period
Thanks
June 15, 2010 at 10:45 am
Test your code before posting it!
try:
CREATE TABLE #Test
(
EmployeeID Int,
LogTime DateTime
)
INSERT INTO #Test
select 1001, '01/01/2010 10:22:36'
union select 1002, '01/01/2010 10:24:37'
union select 1003, '01/01/2010 10:24:38'
union select 1001, '01/01/2010 10:24:39'
union select 1001, '01/01/2010 10:24:40'
union select 1002, '01/01/2010 10:24:45'
union select 1003, '01/01/2010 10:26:45'
--drop table #qupd
select EmployeeID, LogTime, cast(Null as datetime) as PrevLogTime, cast(null as int) as TimeDiff
into #qupd
from #Test
create clustered index cix_qupd on #qupd(EmployeeID, LogTime)
declare @PrevLogTime datetime
declare @TimeDiff int
declare @EmployeeId int
update #qupd set
@PrevLogTime = PrevLogTime = CASE WHEN (EmployeeID = @EmployeeId) THEN @PrevLogTime ELSE NULL END
,@TimeDiff = TimeDiff = DATEDIFF(minute, @PrevLogTime, LogTime)
,@EmployeeId = EmployeeID
,@PrevLogTime = LogTime
option (maxdop 1)
select EmployeeID, LogTime
from #qupd
where PrevLogTime is null or TimeDiff > 1
Please note: you have entries in your sample data for EmployeeID 1001 whcih are more than 1 minute appart, so you should expect them to be shown in results.
June 15, 2010 at 11:39 am
My initial thought is that the easiest as fastest way to do this is to create a computed column of the datetime value where seconds are stripped away, then create a unique index on EmployeeId and the computed column with IGNORE_DUPLICATE_KEY. Values withing the same minute are disallowed but the IGNORE_DUPLICATE_KEY prevents a rollback of the constraint violation. This only work for INSERTs, bot UPDATEs.
I have never used the IGNORE_DUPLICATE_KEY option before, so I can't say if there are any "best practices" against using it. The is a little voice far back in my head that tells me that this is a bad idea though. INSERTS that are not saved without returning any information about it... hmm... It just feels wrong.
June 15, 2010 at 12:13 pm
with cteTest (RowNum, EmpID, LogTime)
as
(
select ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY LogTime ASC),
EmployeeID,
LogTime
from #Test
)
select cc.EmpID,
cc.LogTime
from cteTest cc
left join cteTest cp
on cp.EmpID = cc.EmpID
and cp.RowNum = cc.RowNum - 1
where DATEDIFF(ss, coalesce(cp.LogTime, '01/01/01'), cc.LogTime) > 120
order by cc.EmpID, cc.LogTime
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply