Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 find duplicate values and get timediff

Author  Topic 

mp2_admin
Starting Member

10 Posts

Posted - 2013-12-10 : 10:19:51
Hi. Is there a way to find duplicate values and get the timediff from the start and end of each duplicate.

datetime Tagname value Machine Value Time Diff Minutes
12/9/13 8:55 machine_1 14,423 Machine_1 14,423 5
12/9/13 9:00 machine_1 14,423 Machine_1 14,428 9
12/9/13 9:05 machine_1 14,428 Machine_1 42,743 4
12/9/13 9:10 machine_1 14,428
12/9/13 9:14 machine_1 14,428
12/9/13 11:32 machine_1 42,743
12/9/13 11:36 machine_1 42,743

Thank You

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-10 : 10:46:20
that is the required result ? How does the original data from your table looks like ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 2013-12-10 : 11:01:30
Original data is on left side of posted data.. Outcome is on right side.

quote:
Originally posted by khtan

that is the required result ? How does the original data from your table looks like ?


KH
[spoiler]Time is always against us[/spoiler]



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-10 : 11:14:10
can you be more specific ?

And how do you consider duplicate values ? what are the columns that you consider ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 2013-12-10 : 11:21:49
Sure..

I am looking for duplicate values. I have tagname, datetime, value fields. I would like to pull out the duplicate values and get the timedifference from when each duplicate value started and ended.

Hope I am explaining this correctly

quote:
Originally posted by khtan

can you be more specific ?

And how do you consider duplicate values ? what are the columns that you consider ?


KH
[spoiler]Time is always against us[/spoiler]



Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-10 : 11:30:00
this is what you wanted ?
select	t.[datetime], t.Tagname, t.value, d.TimeDiff
from yourtable t
left join
(
select Tagname, value, TimeDiff = datediff(minute, min([datetime]), max([datetime]))
from yourtable
group by Tagname, value
having count(*) > 1
) d on t.Tagname = d.Tagname
and t.value = d.value



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

mp2_admin
Starting Member

10 Posts

Posted - 2013-12-10 : 12:06:28
Thank You Very Much!
I can get the required output from your QRY.
Go to Top of Page
   

- Advertisement -