Select most recent record
-
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement
This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
-
What are the circumstances under which you wish to discard the less recent value? Is it when it is less than an hour before the previous one? Or is it when some combination of Value1, Value2 and Value3 is not unique?
John
-
I only want to see 24 values each day.
In normal circumstances this is not a problem as the db normally receives 1 new record each hour.
We have no idea why suddenly 2 record were being added one second after the other as this has never happened before and there is nothing in the logfile indicating there was a problem of some sort.
Combinations of values don't have to be unique.
These are the times when records got added on the 14th.
As you can see there is a difference of 1 second between 12:06:13 and 13:06:12 but that doesn't matter.
Problem is indicated in red: we only want to keep the values captured at 19:06:13 because we noticed the values captured at 19:06:12 seem to be just copies of the ones captured at 18:06:12. The actual new values are in the "19:06:13"-record.
14/12/2006 0:06:13
14/12/2006 1:06:13
14/12/2006 2:06:13
14/12/2006 3:06:13
14/12/2006 4:06:13
14/12/2006 5:06:13
14/12/2006 6:06:13
14/12/2006 7:06:13
14/12/2006 8:06:13
14/12/2006 9:06:13
14/12/2006 10:06:13
14/12/2006 11:06:13
14/12/2006 12:06:13
14/12/2006 13:06:12
14/12/2006 14:06:12
14/12/2006 15:06:12
14/12/2006 16:06:12
14/12/2006 17:06:12
14/12/2006 18:06:12
14/12/2006 19:06:12
14/12/2006 19:06:13
14/12/2006 20:06:12
14/12/2006 21:06:12
14/12/2006 22:06:12
14/12/2006 23:06:12
So when we have more than one record being added in - let's say - 10 minutes time, we want to keep the most recent value.
-
Probably what you need, then, is to create a trigger in the table that checks the time of the previous insert, and if it was less than ten minutes ago, deletes the previous row.
John
-
try this, it won't delete the data but should display what you are looking for
SELECT TimeOfCapture, Value1, Value2, Value3) FROM FactoryTable
join (select max(timeofcapture) as maxtoc from factorytable group by convert(varchar,timeofcapture,101),datepart(hh,timeofcapture)
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE())) ) mtoc on timeofcapture = maxtoc
-
With my workload winding down for Christmas (lucky me!!
) I've put this together as an example trigger that you could use:CREATE TABLE tmpSolution(
tTime datetime)
GO
CREATE TRIGGER tmpInsSolution
ON tmpSolution
FOR INSERT
AS
BEGIN
DECLARE @tMaxTime datetime
DECLARE @tNewTime datetime
SELECT @tNewTime = tTime FROM inserted
SELECT @tMaxTime = MAX(tTime) FROM tmpSolution WHERE tTime < @tNewTime
IF DATEDIFF(mi, @tMaxTime, @tNewTime) IS NOT NULL AND DATEDIFF(mi, @tMaxTime, @tNewTime) < 10
DELETE FROM tmpSolution WHERE tTime = @tMaxTime
END
GO
-- Normal operation
INSERT INTO tmpSolution VALUES( '2006/12/25 00:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 01:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 02:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 03:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 04:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 05:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 06:00:00')
SELECT * FROM tmpSolution
GO
-- Second record within 10 minutes
INSERT INTO tmpSolution VALUES( '2006/12/25 06:06:13')
SELECT * FROM tmpSolution
GO
-- Resume normal operation
INSERT INTO tmpSolution VALUES( '2006/12/25 07:00:00')
INSERT INTO tmpSolution VALUES( '2006/12/25 08:00:00')
SELECT * FROM tmpSolution
GO
DROP TABLE tmpSolution
GO
Hope this helps!
-
Use a self join. A self join is when you join a table to itself.
To see all of the rows considered duplicates:
Select
Copy1.*
From
MyTable As Copy1
Inner
Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0
To see all of the rows that will be deleted:
Select Copy1.*
From
MyTable As Copy1
Inner
Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0
Where
Copy1.MyDateField < Copy2.MyDateField
To delete the rows:
Delete
Copy1
From
MyTable As Copy1
Inner
Join MyTable As Copy2 On DateDiff(minute, Copy1.MyDateField, Copy2.MyDateField) < 55 And DateDiff(hour, Copy1.MyDateField, Copy2.MyDateField) = 0
Where
Copy1.MyDateField < Copy2.MyDateField
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply