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

Reject duplicate Time within 01 minute Expand / Collapse
Author
Message
Posted Sunday, June 13, 2010 9:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 3:26 AM
Points: 2, Visits: 7
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
Post #936599
Posted Sunday, June 13, 2010 11:27 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:28 AM
Points: 42,468, Visits: 35,538
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #936607
Posted Monday, June 14, 2010 1:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 21, 2011 3:26 AM
Points: 2, Visits: 7
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
Post #936637
Posted Tuesday, June 15, 2010 10:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #937686
Posted Tuesday, June 15, 2010 11:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 1,839, Visits: 3,421
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.
Post #937725
Posted Tuesday, June 15, 2010 12:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 1:49 PM
Points: 1,104, Visits: 1,174
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



└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #937745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse