SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reject duplicate Time within 01 minute


Reject duplicate Time within 01 minute

Author
Message
hahs.mohamed
hahs.mohamed
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213976 Visits: 46264
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

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


hahs.mohamed
hahs.mohamed
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11810 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3569 Visits: 3575
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.
Ben Teraberry
Ben Teraberry
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2777 Visits: 1199
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search