Calculating Dates

Question

I have this problem: List of customers with their next scheduled, reoccurring appointment, that is either yearly, monthly, or quarterly.

The tables\columns I have are:

customer
   customer_ID
service
   customer_ID
   service_RecID
Resource
   service_RecID
   Recurrence_RecID
   Date_Time_Start
Recurrence
   Recurrence_RecID
   RecurType
   RecurInterval
   DaysOfWeek
   AbsDayNbr
   SelectInterval

It is modeled such that when the schedule is setup, the date_start_time is the date of when the first reoccurring appointment took place. Ex.

Recurrence_RecID = 10
RecurType = m (could be y, or d as well for yearly or daily)
RecurInterval = 6 (if recurType = y, this would mean every 6 years)

Given that the system generates these nightly, how would I write a query to calculate the next scheduled appointment, for each customer? I originally thought of using the Resource.Date_Time_Start and just cycling through until a variable nextAppointment >= today(), but is it good practice to run loops in SQL?

If anymore info is needed, let me know. Thank you much!

Edit: I will make a sqlfiddle.

Answer 1

I would suggest using a sub-query as opposed to looping. More efficient that way. This may not be exact but something like...

SELECT 
    *
FROM
(      
SELECT
    customer.customer_id,
    service.service_RecID,
    Resource.Date_Time_Start,
    Recurrence.Recurrence_RecID,
    RecurType,
    RecurInterval,
    DaysOfWeek,
    AbsDayNbr,
    SelectInterval,
    NextAppointmentDate=
    CASE 
        WHEN RecurType='m' THEN DATEADD(MONTH,RecurInterval,Resource.Date_Time_Start)
        WHEN RecurType='y' THEN DATEADD(YEAR,RecurInterval,Resource.Date_Time_Start)
    ELSE 
        NULL
    END        
FROM
    Recurrence
    INNER JOIN Resource ON Resource.Recurrence_RecID=Recurrence.Recurrence_RecID
    INNER JOIN service ON service.service_RecID=Resource.service_RecID
    INNER JOIN customer ON customer.customer_ID=service.customerID
)AS X
WHERE
    NextAppointmentDate>=GETDATE()
ORDER BY Fields...