Qry duplicate values to a certain point? 1

[mptwoadmin]

Hi i am attempting gather the amount of minutes that have occurred in the duplicate values in the following data. The column headers are my Db column names. I am trying to count the minutes between "start count here" & "end count here" only. I am ordering my datetime column desc order. If i have duplicate data past my "end count here" i do not care about only this first occurrence. I have tried running a qry by removing duplicates but my qry looks at my whole two hour time frame.



Datetime Tagname Value
3/21/12 15:43 Machine 100585 Start count here.
3/21/12 15:42 Machine 100585
3/21/12 15:41 Machine 100585
3/21/12 15:40 Machine 100585
3/21/12 15:39 Machine 100585
3/21/12 15:37 Machine 100585
3/21/12 15:36 Machine 100585
3/21/12 15:35 Machine 100585
3/21/12 15:34 Machine 100585
3/21/12 15:33 Machine 100585
3/21/12 15:31 Machine 100585
3/21/12 15:30 Machine 100585
3/21/12 15:29 Machine 100585
3/21/12 15:28 Machine 100585
3/21/12 15:27 Machine 100565
3/21/12 15:25 Machine 100548 End count here
3/21/12 15:24 Machine 100544
3/21/12 15:23 Machine 100360
3/21/12 15:22 Machine 100078
3/21/12 15:21 Machine 99836


This is my qry i ran to get duplicates.

select DATEDIFF(MINUTE, MIN(datetime), max(datetime)) as end_downtime
from analoghistory
where TagName = 'machine'
and (datetime < '03/21/2012 15:45'
and datetime > '03/21/2012 13:45')
group by value having count(value) > 1

The duplicate values may change from many to a few, i tried to add only show me duplicates greater than 5 and so on..

Thank You

 

[simian336]

I shortened your field names...

with cte as
(
select tag, val, max(dt) as maxdt, min(dt) as mindt
from yourtable
group by tag, val
)
select tag, val, DATEDIFF(MINUTE, mindt, maxdt) as end_downtime
from cte

Simi

 

[mptwoadmin]

Thanks ill give it a try