Custom Query in SQL Server

Category: sql server datamining

Question

Anu_1986 on Sun, 18 Mar 2018 08:05:24


Hi,

I am using the below query and send automatic email to our team in every 15 minutes.

'Select * from [Database].[dbo].[TableName] where START_TIME = Convert(date, getdate()) and OPTION=1'

The query is like ...we had a column named "option" inside the table and i am querying to take the value 1 from the column and sending every 15 minutes. The issue is that the query returns the values from today's date in every email.

My question is that "is there any way to take only the values which comes in last 15 mns from the column to avoid re sending the old values". (ie, output should include the updated data instead of appending to the previous list)

Regards

Ansar


 


Replies

Visakh16 on Sun, 18 Mar 2018 09:02:07


to get last 15 minutes data you can use this

Select * from [Database].[dbo].[TableName] where START_TIME >= DATEADD(minute,-15,getdate()) and OPTION=1

But a better method to avoid overlaps is this

have a table which captures the key value of the details which are captured and send each time in a separate table

then use it to compare and send the only the new values the next time

i.e say you've keycolumn which is unique 

then have a table processedentries with keycolumn and datevalue

and populate it each time like 

INSERT ProcessedEntries
SELECT KeyColumn,GETDATE()
FROM [Database].[dbo].[TableName] t
WHERE START_TIME >= DATEADD(minute,-15,getdate())
AND NOT EXISTS (
SELECT 1
FROM [Database].[dbo].[TableName]
WHERE KeyColumn = t.KeyColumn
)

then make main query like

Select * from [Database].[dbo].[TableName]  t
where START_TIME >= DATEADD(minute,-15,getdate()) 
and OPTION=1
AND NOT EXISTS (
SELECT 1
FROM ProcessedEntries
WHERE KeyColumn = t.KeyColumn
)
Then it will take only the new entries each time


Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page


Anu_1986 on Sun, 18 Mar 2018 10:52:18


Thanks Visakh...Really Appreciate.

Visakh16 on Sun, 18 Mar 2018 11:07:06


Thanks Visakh...Really Appreciate.
welcome :)