Recurring events

• jerrym

  Old Hand

  Points: 347

  More actions

  August 5, 2008 at 4:02 am

  #189031

  i'm hoping someone would give me a few pointers on how i can achieve/implement the following from a database /tables perspective.

  My issue involves an Asset register and Maintenance application where i'm not real sure on how to handle recurring events like maintenance of a motor car. the record form can easily capture information such as Frequency, StartDate, EndDate, etc similar to Outlook.

  But how do i handle and keep track of the occurance of events?

  any help would be greatly appreciated.

  regards

  jerry

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  August 5, 2008 at 4:23 am

  #853083

  Seems like you want us to help you in designing tables. My suggestion is that you do a rough design and then post it here. It will give us more details to work with and you can have better results.

  "Keep Trying"

• jerrym

  Old Hand

  Points: 347

  More actions

  August 5, 2008 at 5:55 am

  #853112

  hi chirag

  Equipment table with fields (equipid, make, model,serial, vendor,date,purchcost)

  Maintenance table with fields (maint_id, equipid[fk], DateStart, description, frequency, PeriodType, NoOfRecurrences,ScheduleDate)

  once data is populated into both tables, i'm not sure how to use the info to generate say a list report for the next 6 months a listing of scheduled maintenace items or events.

  For example; if data for a motor car is entered in the Maintenance table with the following;

  maint_id = 1001

  equipid = 505

  DateStart=08/05/2008

  description= log book service

  frequency = 6 (this is an integer field)

  PeriodType = Month (listbox showing Daily, weekly, monthly, annually)

  NoOfRecurrances = 10 (integer field)

  ScheduleDate = calculated field (this is where i probably need some guidance)

  hope you can help. thanks.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  August 8, 2008 at 5:55 am

  #855049

  Hi

  sorry for the delay.

  to calculate the scheduledate

  select * from

  Maintenance where dateadd(mm,6,datestart) for maint_id = "somevalue" and datestart = getdate().

  similarly for the week,year,days etc.

  Put this in a proc and put the proc in a job that runs everyday.

  Or else

  If you have a end date to show when the maintenance period ends -- calculate all the schedule dates between

  datestart and the end date.

  hope i have helped you..

  "Keep Trying"