1

Hard to put my question into a title. What I am looking for is a way to see which tasks were scheduled to run in the past 15 minutes.

I currently have a table that shows the window that a task should run and how often it should be kicked off within that window. However, I have no experience with this type of query. Hopefully somebody can help out a bit. I should also say that I'd like to do this without creating extra tables

Schedule Table

| taskID | startTime | endTime | everyMinutes |
|________|___________|_________|______________|
| 135467 |    8:00   |  20:00  |      15      |
| 675314 |    7:15   |  13:45  |      30      |

For example, in the above table, if the time was 9:18, the query would return both tasks

| taskID | scheduledTime|
|________|______________|
| 135467 |     9:15     |
| 675314 |     9:15     |

Where as if it was ran at 9:35 the query would only return 1 result

| taskID | scheduledTime|
|________|______________|
| 135467 |     9:30     |
Andriy M
  • 76,112
  • 17
  • 94
  • 154
mhopkins321
  • 2,993
  • 15
  • 56
  • 83
  • It might be a good idea to specify what SQL product you are using, because different products offer different sets of date/time functions in their respective variations of SQL. – Andriy M Aug 28 '12 at 22:07

2 Answers2

2

You could use a numbers table to expand the Schedule table into a series of taskID, scheduledTime rows, then filter the rows to return only those that have scheduledTime within the last 15 minutes:

WITH expanded AS (
  SELECT
    s.taskID,
    scheduledTime = DATEADD(MINUTE, s.everyMinutes * n.N, s.startTime)
  FROM Schedule s
  INNER JOIN Numbers n
  ON n.N BETWEEN 0 AND DATEDIFF(MINUTE, s.startTime, s.endTime) / s.everyMinutes
)
SELECT e.*
FROM expanded e
CROSS APPLY (SELECT CAST(GETDATE() AS time)) x (now)
WHERE e.scheduledTime BETWEEN DATEADD(MINUTE, -15, x.now) AND x.now
;

You can try this query, as well as play with it, at SQL Fiddle.

Community
  • 1
  • 1
Andriy M
  • 76,112
  • 17
  • 94
  • 154
-1

Point #1: You should use cron jobs to schedule your tasks, and use a database table to keep a history of the jobs that actually ran (start time, end time, result etc.) That way you get much more accurate reporting. All jobs have exceptions (e.g. some jobs don't run on weekends, some don't run on public holidays) so using the schedule to deduce what jobs ran can lead you to false assumptions.

Point #2: I don't recommend doing this in the database. It will be easier to code and faster to run in C# or PHP, than in the database

Vishal Bardoloi
  • 652
  • 5
  • 18
  • Point 1: I use moveIT for executing tasks. It writes when the jobs actually ran to the database. There seems to be a delay from when the task is supposed to start and when it actually starts. I need to find out when tasks are schedules so I can do some analysis. Point 2: I didn't ask how I should run tasks or where I should record them to, but rather how to fix this issue using sql – mhopkins321 Aug 28 '12 at 17:07
  • Great, glad you clarified that the first time round. I was just trying to point out that you could do them faster and probably easier if you did them in code v/s sql. – Vishal Bardoloi Aug 28 '12 at 18:11
  • I am not allowed to deviate away from sql. Unfortunately I already have a script to do this with powershell. But that's not sql. So like above, where I stated for how to do this with a query, I can only do this with sql – mhopkins321 Aug 28 '12 at 19:32