SQL Agent schdule a job based on company period

Hello Experts Exchange
I have a table in SQL Server called Company Period, it has every day of the year and the period that day is in. Please see attached excel file for a sample data.

I need to schedule a SQL Agent Job to run on the second day of a new period for the pervious period.

So for examples on the 02/02/2015 which is the second day for period 2 I need to run it for period 1.  I need to do this for every month of the year.

How would I configure the SQL Agent to be able to do this?

Regards

SQLSearcher
Company-Period.xls
SQLSearcherAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Run a "parent" job every day, which decides whether to run this job or not.. and start it from this "parent" job, when necessary.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It' s easy to configure the job in SQL Agent to run every month and since the period correspond to the month number you just need to filter the query to something like:
SELECT t.*
FROM CompanyPeriod t
WHERE (t.Period=MONTH(GETDATE())-1 AND t.Year=YEAR(GETDATE()))
    OR (t.Period=12 AND t.Year=YEAR(GETDATE())-1 AND MONTH(GETDATE())=1)

Open in new window

NOTE: The OR is needed for the cases when you want to process December of last year and you're already in January and the year just changed.
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Hi you can write sql query to check the date from the table and if the condition fulfills you can write the code in the sql agent job to perform the task.

You can schedule it for every day and it will check the date in the T sql code and if the date fulfills the code return in the if condition fires.

If (date = 'value')
begin
your code to perform operation
end
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SQLSearcherAuthor Commented:
Hello Experts
I think I need to run a job everyday to check, but how do I get the code to work on the second day of a new period?

Do you think I need to add a day number for a period and when it is 2 that is the date the code runs?

Regards

SQLSearcher
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I think I need to run a job everyday to check
Why?

how do I get the code to work on the second day of a new period?
Using Job Scheduler like this (check the frequency)?
JobSchedule.PNG
Do you think I need to add a day number for a period and when it is 2 that is the date the code runs?
No need if you have the correct schedule set.
0
SQLSearcherAuthor Commented:
Hello Vitor
Thank you for your comment, but the period does not always match up to the month, for example period 4 in 2015 starts on 05/04/2015, so the second day is the 06/04/2015, so in that case the day would be 6.

So what I think I need is a script that finds the second day of a period, and when that matches todays date I want the job to run.  But I don't know how to configure this.

Regards

SQLSearcher
0
ste5anSenior DeveloperCommented:
Thus you need to run your parent job every day to test this.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
but the period does not always match up to the month, for example period 4 in 2015 starts on 05/04/2015, so the second day is the 06/04/2015, so in that case the day would be 6.
So, what's the formula? The first Monday of a month? If so you can still schedule it accordlying:
Schedule.PNG
0
SQLSearcherAuthor Commented:
Hello ste5an
Can you provide more details on how I can do it with a parent Job please?

Regards

SQLSearcher
0
ste5anSenior DeveloperCommented:
Create a job which is run each day. Check you period table whether you need to run your job. Do this in a store procedure to calculate  it. When it should not run, use RAISERROR(). Cause the Execute T-SQL Statement has no return mechanism.

Connect the  Execute T-SQL Statement with a Execute SQL Server Agent Job Task. Select your existing job there.

When the existing job is only calling a stored procedure, then instead of using RAISERROR call that directly.
0
Scott PletcherSenior DBACommented:
I'd have two jobs.  

1) A controller/"header" job that just checks the date and starts the main job if it needs to run.  This job is schedule to run every day.
2) The main job that does the processing.  It is not scheduled, but only started ("called") when needed.


Here's the T-SQL code for the controller job step:


DECLARE @Todays_Period_Day_Number int

SELECT @Todays_Period_Day_Number = Period_Day_Number
FROM (
    SELECT cp.Date, ROW_NUMBER() OVER(ORDER BY Date) AS Period_Day_Number
    FROM Company_Periods cp
    WHERE
        cp.Period_Count_AV = (
            SELECT cp2.Period_Count_AV
            FROM Company_Periods cp2
            WHERE cp2.Date = CAST(GETDATE() AS date)
            )
) AS derived
WHERE
    Date = CAST(GETDATE() AS date)

IF @Todays_Period_Day_Number = 2
    EXEC msdb.dbo.sp_start_job @job_name = 'main_job'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLSearcherAuthor Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.