Need Help Filtering SQL Results for Report

Ignition
,
#1

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

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?

#2

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

#3

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];

#4

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.

#5

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.

#6

Try something like this:

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.

#7

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 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!

#8

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.

#9

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.