CODEWITHSUNDEEP
sqlsql-server
Efrat
Efrat

Reputation: 27

SQL get next date time

I Have 3 Tables in SQL SERVER

  1. JOBS
JobID LastRunDateTime
1     01/02/2015
2     05/02/2015
3     20/01/2015
  1. JobsDays - the days that the jobs need to run
JobID DayNum
1     1
1     2
1     5
2     2
2     6
3     1
3     2
3     3
  1. JobTimes - the hours that the jobs nees to run
JobID TimeNum
1     12
1     13
2     10
2     20
3     7

I need to calculate the next time the job need to run.

I start to find the next date (without time) like this:

DECLARE @CurrentDateTime DATETIME
SET @CurrentDateTime = GETDATE()

SELECT j.JobID,
       MIN(DATEADD(DAY, (DATEDIFF(DAY, ((jday.DayNum + 5 ) % 7 ), @CurrentDateTime) / 7) * 7 + 7, ((jday.DayNum + 5 ) % 7)))
     AS NEXTDATE
FROM Jobs j
    JOIN JobDays jday on j.JobID = jday.JobID
    group by j.JobID

I get the next date to run, but I need the full datetime of the next run.

Thanks

Upvotes: 0

Views: 1193

Answers (2)

GarethD
GarethD

Reputation: 69749

I would start by getting the first day of the current week:

SET DATEFIRST 1;
DECLARE @WeekStart DATETIME = DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE));

Which when run today (5th Feb) gives 2015-02-05

Then you will need a cartesian product of days and times for each job, e.g. for job 1 you would end up with:

DayNum  Time
---------------
1       12
1       13
2       12
2       13
5       12
5       13

This would be done by using

SELECT  d.DayNum, t.TimeNum
FROM    Jobdays AS d
        INNER JOIN JobTimes AS t
            ON t.JobID = d.JobID
WHERE   d.JobID = 1

Then it is just a case of adding these days and times to the start of the week:

DayNum  TimeNume    Date/Time
---------------------------------------
1       12          2015-02-03 12:00:00
1       13          2015-02-03 13:00:00
2       12          2015-02-04 12:00:00
2       13          2015-02-04 13:00:00
5       12          2015-02-07 12:00:00
5       13          2015-02-07 13:00:00

Using:

DATEADD(HOUR, t.TimeNum, DATEADD(DAY, d.DayNum - 1, @WeekStart))

The problem is that right at the end of the week, you will end up with going into the next week to find the next run date, so you need to generate two weeks worth of dates and times by adding a further join:

SELECT  d.DayNum, 
        t.TimeNum,
        RunDateTime = DATEADD(WEEK, w.WeekNum, DATEADD(HOUR, t.TimeNum, DATEADD(DAY, d.DayNum - 1, @WeekStart)))
FROM    Jobdays AS d
        INNER JOIN JobTimes AS t
            ON t.JobID = d.JobID
        CROSS JOIN (VALUES (0), (1)) AS w (WeekNum)
WHERE   d.JobID = 1;

So bringing it all together, you end up with

DECLARE @WeekStart DATETIME = DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE));
WITH JobDays AS
(   SELECT  JobID, DayNum
    FROM    (VALUES (1, 1), (1, 2), (1, 5), (2, 2), (2, 6), (3, 1), (3, 2), (3, 3)) t (JobID, DayNum)
), JObTimes AS
(   SELECT  JObID, TimeNum
    FROM    (VALUES (1, 12), (1, 13), (2, 10), (2, 20), (3, 7)) t (JobID, TimeNum)
), RunDates AS
(   SELECT  d.JobID,
            RunDateTime = DATEADD(WEEK, w.WeekNum, DATEADD(HOUR, t.TimeNum, DATEADD(DAY, d.DayNum - 1, @WeekStart)))
    FROM    Jobdays AS d
            INNER JOIN JobTimes AS t
                ON t.JobID = d.JobID
            CROSS JOIN (VALUES (0), (1)) AS w (WeekNum)
)
SELECT  JobID,
        NextRunDateTime = MIN(RunDateTime),
        NextRunDate = CAST(MIN(RunDateTime) AS DATE),
        NextRunTime = CAST(MIN(RunDateTime) AS TIME)
FROM    RunDates
WHERE   RunDateTime > GETDATE()
GROUP BY JobID;

Which gives:

JobID |    NextRunDateTime  | NextRunDate| NextRunTime
------+---------------------+------------+---------
1     | 2015-02-05 12:00:00 | 2015-02-05 | 12:00:00
2     | 2015-02-06 10:00:00 | 2015-02-06 | 10:00:00
3     | 2015-02-08 07:00:00 | 2015-02-08 | 07:00:00

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

Here you go.. write as:

DATEPART(HH,GETDATE()) + (MIN(jTime.TimeNum -DATEPART(HH,GETDATE())))AS NEXTTIME

as:

SELECT distinct Job.JobID,
       MIN(DATEADD(DAY, (DATEDIFF(DAY, ((jday.DayNum + 5 ) % 7 )
      , @CurrentDateTime) / 7) * 7 + 7, ((jday.DayNum + 5 ) % 7)))
      AS NEXTDATE
      ,DATEPART(HH,GETDATE()) 
      + (MIN(jTime.TimeNum -DATEPART(HH,GETDATE())))AS NEXTTIME
FROM @Jobs job
    JOIN @JobsDays jday on job.JobID = jday.JobID
    JOIN @JobsTimes jTime ON jday.JobID = jTime.JobID
 group by job.JobID

Upvotes: 0

Related Questions