# Need Help Filtering SQL Results for Report

**URL:** <https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256>\
**Category:** Ignition\
**Tags:** namedqueries, database\
**Created:** [January 31, 2019, 3:32pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256 "2019-01-31T15:32:38Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![jordan.richardson](https://avatars.discourse-cdn.com/v4/letter/j/94ad74/32.png) [@jordan.richardson](https://forum.inductiveautomation.com/u/jordan.richardson)\
**Post date:** [January 31, 2019, 3:32pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/1 "2019-01-31T15:32:38Z")

</div>

I am collecting estop data to record how many times estops are being pressed in a month. The issue I have ran into is that because the data is logged when the tag value goes low it captures the line being locked out as a total estop. This is skewing my results: ![Estop%20data](https://us1.discourse-cdn.com/inductiveautomation/original/2X/f/f5fca048c6a2d98b92af2a7590dab0fca0b9ff2b.png)

The Query I am running:

`SELECT EstopActivity.Department, EstopActivity.LineNumber, EstopActivity.EStop, EstopActivity.[TimeStamp] FROM EstopActivity WHERE Department= 'Primary Extrusion'`

Is there something I can add to this query in order to only pull estop data with a “unique” Timestamp?

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [February 1, 2019, 10:40pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/2 "2019-02-01T22:40:54Z")

</div>

You'll most likely have to either drop down into a self join or you can use a CTE  
The date range in there is just for cutting down the records.  
If your timestamp field is an actual timestamp and not just a datetime, you will have to format it to a shortened date otherwise milliseconds will come into play.

> WITH cte AS  
> (  
> SELECT \*,  
> ROW\_NUMBER() OVER (PARTITION BY TimeStamp ORDER BY TimeStamp DESC) AS rn  
> FROM EstopActivity  
> WHERE TimeStamp \>='2018-12-01 00:00:00' and TimeStamp \< '12/2/2018'  
> )  
> SELECT \*  
> FROM cte  
> WHERE rn = 1

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [February 4, 2019, 2:17pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/3 "2019-02-04T14:17:18Z")

</div>

Perhaps I'm misunderstanding / oversimplifying, but could you not just use the **DISTINCT** function? Something like this.

> SELECT EstopActivity.Department, EstopActivity.LineNumber, EstopActivity.EStop, DISTINCT EstopActivity.[TimeStamp] FROM EstopActivity WHERE Department= 'Primary Extrusion';

Or if that doesn't work, what about using group by?

> SELECT EstopActivity.Department, EstopActivity.LineNumber, EstopActivity.EStop, EstopActivity.[TimeStamp] FROM EstopActivity WHERE Department= 'Primary Extrusion' GROUP BY EstopActivity.[TimeStamp];

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 4, 2019, 2:23pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/4 "2019-02-04T14:23:42Z")

</div>

I looks to me like you really have a problem in uniquely identifying which button is responsible for any given estop event and only recording that one. That often requires some form of ‘first out’ logic in your PLC.

---

<div class="post-metadata">

**Author:** ![jordan.richardson](https://avatars.discourse-cdn.com/v4/letter/j/94ad74/32.png) [@jordan.richardson](https://forum.inductiveautomation.com/u/jordan.richardson)\
**Post date:** [February 6, 2019, 2:15pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/5 "2019-02-06T14:15:59Z")

</div>

The Timestamp I am using is just a datetime. I grab it from a system tag in order to add it into table. I guess unique wasn't the right word to use. I would like to be able to scan the data, and see that there are some value with a "duplicate" timestamp value then toss those out. I have found a query that gets close

> SELECT max([LineNumber]) AS linenumber, COUNT([TimeStamp])  
> FROM [Ignition].[dbo].[EstopActivity]  
> GROUP BY [TimeStamp]  
> HAVING COUNT([TimeStamp]) \> 1

but now I am having trouble deleting the rows where that value is greater than 1. Also I forgot to mention, this a monthly report. So it's no problem to scan the entire table to find the values. I run a script to add it into a yearly table, then clear the whole thing.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 6, 2019, 2:57pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/6 "2019-02-06T14:57:45Z")

</div>

Try something like this:

```auto
Select whatever......
From (
	Select whatever....., lag(TimeStamp) Over (Order By TimeStamp) as priorTS
	From EstopActivity
) inner
Where TimeStamp <> priorTS
Order By TimeStamp

```

BTW, you really shouldn’t be using column names that are reserved words in the SQL standard.

---

<div class="post-metadata">

**Author:** ![jordan.richardson](https://avatars.discourse-cdn.com/v4/letter/j/94ad74/32.png) [@jordan.richardson](https://forum.inductiveautomation.com/u/jordan.richardson)\
**Post date:** [February 6, 2019, 3:16pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/7 "2019-02-06T15:16:45Z")

</div>

You are very much correct, that is a mistake that I'll chop up to being very new to sql. I ended up finding the query I needed on [this](http://forums.databasejournal.com/showthread.php?42707-DELETE-items-where-COUNT-gt-1-(MS-SQL)) forum. The query that ended up working was:

> delete d from [Ignition].[dbo].[EstopActivity] d join  
> (  
> SELECT [TimeStamp] FROM [Ignition].[dbo].[EstopActivity]  
> GROUP BY [TimeStamp]  
> HAVING COUNT([TimeStamp])\>1) as tm  
> on tm.[TimeStamp]=d.[TimeStamp]

Thank you for your help!

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [February 6, 2019, 3:19pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/8 "2019-02-06T15:19:13Z")

</div>

I don’t think you should run that delete query. I looks like it’ll delete all of the rows that have duplicates, not just the extras.

---

<div class="post-metadata">

**Author:** ![jordan.richardson](https://avatars.discourse-cdn.com/v4/letter/j/94ad74/32.png) [@jordan.richardson](https://forum.inductiveautomation.com/u/jordan.richardson)\
**Post date:** [February 6, 2019, 6:40pm UTC](https://forum.inductiveautomation.com/t/need-help-filtering-sql-results-for-report/22256/9 "2019-02-06T18:40:31Z")

</div>

That’s what I need it to do. The only reason there are duplicates is because either the whole line was locked out, or a portion of the line was locked out. When that happens the bit value goes low, the line thinks it has estopped, and the location of the estop is recorded along with the timestamp. Since this isn’t a true estop, I would like to get rid of that data.