Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

sql query help

I need help creating the sql query to handle recurring tasks. I am creating a task which can run once, weekly bi-weekly, monthly , quarterly, annually.

The task table have
taskID,
friquencyID,
 name,
taskStartdate
taskStatus
0
erikTsomik
Asked:
erikTsomik
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'task'.  Offhand SQL Server Agent allows for execution of SSIS packages/stored procedures/executables/whatever on a scheduled basis.   Also, having a SQL Server Calendar Table can help you with the date math.
1
 
erikTsomikSystem Architect, CF programmer Author Commented:
The task is the task that get assigned to the client in either fixed date or can be recurring task that repeats daily,weekly bi-weekly, monthly , quarterly, annually.

For example, do the car oil change it a recurring task that repeats  every 6 month.
0
 
PortletPaulCommented:
I think you need to define your requirements with more precision.

e.g.
Do you have table(s) for these tasks already? or are you asking for assistance in defining the table(s)?
If you have the table(s) please provide the definition(s) of them

Do you want new rows created for "recurring tasks"?
 If yes, how far ahead should this be done?
 Would a "look ahead" period differ for each of these:
             daily,weekly bi-weekly, monthly , quarterly, annually.

It looks to me like you need a stored procedure that you can run as a job, it would scan the task definitions and generate a set of rows to comply with those. But the rules need to be defined - in detail.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
erikTsomikSystem Architect, CF programmer Author Commented:
Here is the table definition
taskID,
 name,
taskStartdate
taskStatus
repeat  if 0 no repeat, 1-  daily,2-weekly, 3-bi-weekly, 4-monthly ,5- quarterly, 6-annually.
0
 
PortletPaulCommented:
Good start, but what are you expecting if "today" is 2016-09-01 and  I enter these rows?

askID,  name, taskStartdate, taskStatus
1, aaaa, 2016-09-01, 0
1, bbbb, 2016-09-01,1
1, ccccc, 2016-09-01, 2
1, dddd, 2016-09-01, 3
1, eeee, 2016-09-01, 4
1, fffffff, 2016-09-01, 5
1, gggg, 2016-09-01, 6

What do you want to happen?
e.g.
more rows get generated in that same table?
rows get added to a different table?
how many rows for EACH taskStatus value will get entered
(e.g. for annual is it just one extra row)

BE SPECIFIC please (& using examples is better than just words alone)
0
 
Brad FeatherstoneCommented:
If you are using SQL Server, take a look at creating an SQL Server Agent Job and assigning it to a schedule.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Hi Brad - Welcome to Experts Exchange.  Just so you know, the question has already been answered, and your comment is identical to the first comment provided.  

Feel free to comment in questions as you wish, but please make sure it's not a repeat point.

Thanks, and again welcome.
Jimbo
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now