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.
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 Minutes12/9/13 8:55 machine_1 14,423 Machine_1 14,423 512/9/13 9:00 machine_1 14,423 Machine_1 14,428 912/9/13 9:05 machine_1 14,428 Machine_1 42,743 412/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] |
|
|
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]
|
|
|
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] |
|
|
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 correctlyquote: 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]
|
|
|
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.TimeDifffrom 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] |
|
|
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. |
|
|
|
|
|
|
|