query to check count in last 30 min interval

• mcfarlandparkway

  SSCertifiable

  Points: 7701

  More actions

  December 19, 2018 at 2:19 pm

  #376665

  I am trying to set up job - first it retrieves the count if records for that day in the last 30  min. for every min we need to query no of records for that day.
  i have to get count and store it
  next task is to send email/ alert to the team saying that the count has not gone up in 30 min.

  SELECT COUNT(*)

   FROMdbo.MemberDetails

  WHERE Cdate <  GetDate()

  AND Cdate >dateadd(minute, -30, GetDate())

  Is this right way to query? and can i use smtp mail task to send alerts? also how to store count is it in variable?

• Mike01

  SSChampion

  Points: 11413

  More actions

  December 20, 2018 at 10:41 am

  #2016975

  there's alot of things wrong with this.  First and foremost is what happens if it fails?  You will have records skipped.  Second, why?  This would probably be better as an ssrs report.  But if you truly need this to be a sql agent job, then you will need a place to store the last run date and time as well as a place to store the historical data if needed which includes the number of records it found and when, etc....  Then you can have the process that reads the last run date and gets the count from that date and time to current.  This way, if it fails, it could go back an hour, 2 hours, whatever the last run was.

  For better, quicker answers, click on the following...
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  For better answers on performance questions, click on the following...
  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738150

  More actions

  December 21, 2018 at 1:54 pm

  #2017134

  Don't use GETDATE(), use SYSDATETIME(). It's the more modern function, though in practicality they work the same.

  Second, I assume that if this fails, you don't really care as the next run might catch a change. If that's not true, you'll need some checking on your process to ensure it runs, or some way to note that runs might have been missed.

  Third, for storing this, you don't want a variable. Each run is separate, so this doesn't help. Instead, you want to get this value into table. Something like:
  
-- create table once
create table CountTraker (
  CountTrackerKey datetime2(3) 
-- query code
INSERT dbo.CountTracker
  (
   CountTrackerKey
  , CountValue
  )
SELECT SYSDATETIME()
 , COUNT(*)
  FROM dbo.MemberDetails
    WHERE cDate < SYSDATETIME()
    AND cDate >= DATEADD(MINUTE, -30, SYSDATETIME())
    

  Next, you'll want some query that looks at the last two entries in CountTracker (or some other set of values) and determines if they have increased. If so, then DBMail can send a message to people. I like tracking things like this as once someone gets an alert, they can look for the data to verify.

  You could also count a number of intervals and perhaps MERGE in the counts to capture any missing values if they were lost. I'd rather actually use whatever data I have to get the next interval rather than a set 30, since if my job runs at strange intervals, I might not want that. Or I want to set specific intervals, like 12:00-12:30, 12:30.0001-1:00, etc.