SQL Query ? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query ?

Author

Topic

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-03 : 13:51:25

1) I Want to find out number of records are inserted in table from last 5 minutes

2) Also I wanna to find out date and time for any last insert, Update action performed on table

Thanx in Advance

Dina Satam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 13:53:18

SELECT *
FROM Table1
WHERE Col1 >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)

SELECT MAX(Col1)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 13:54:42

UPDATE information can only be handled if you have some auditing on table.

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-03 : 13:56:55

If I dont have any field which is inserting current date and time then ?

Dina Satam

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-03 : 13:57:56

It can also be handled if the updates come from an application that updates a modified date column on changes.

------------------------
Future guru in the making.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 14:00:07

If you don't store time information yourself about insert and update, the only way to get the information you want would be through a third party log reader.
This is not to recommend if you want this done on a regular basis. Auditing is the way to go.

You can create a trigger which insert data about the ID and time for any update and insert, into a logtable.

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-03 : 14:02:51

Brothers, Thing is that i want to use this query for Prognosys Alerts which will Alert thru sms if there is no insertion and updation in a table for last five minutes. Even if I have Date field in my table but i want to use getdate() -5 minutes which will solve my problem

Dina Satam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 14:07:44

Easy enough. Something like this

CREATE TABLE TableLog (ID INT IDENTITY PRIMARY KEY, dt DATETIME)
GO
CREATE TRIGGER trgLog ON <YourTableNameHere> FOR UPDATE, INSERT
AS

UPDATE TableLog SET dt = CURRENT_TIMESTAMP
IF @@ROWCOUNT = 0
INSERT TableLog SELECT CURRENT_TIMESTAMP
GO

Now create a JOB that is run every 2.5 minutes which call a stored procedure

CREATE PROCEDURE uspLogCheck
AS

IF EXISTS (SELECT * FROM TableLog WHERE dt >= DATEADD(MINUTE, -5, CURRENT_TIMESTAMP)
BEGIN
-- Insert code here for sending email
END

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-03 : 14:08:46

By The Way, PESO thanx for query. I will try but want to confirm that COL1 represent the field having datetime datatype ?

Dina Satam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-03 : 14:12:38

Col1 is meant to represent YOUR datetime column in YOUR environment.
I know nothing about your table so I used Col1 for explanation.

E 12°55'05.25"
N 56°04'39.16"

dineshsatam
Starting Member

19 Posts

Posted - 2007-09-03 : 14:14:01

Really Thankful PESO, But I cant add any table or proc in database. Cause in my company SOX audit goes so any change in table or addition of table there is long procedure as we have to raise CCF [Change Control Form] then it must approved in CCB [Change Control Board].
So really thankful for ur hardwork.
I was of thinking single query will give me count

Dina Satam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources