Estimate New Time

I have a ordersystem with repeating orders. Each order has a orderline


with a frequency and a intervaltype (day, week, month and so on) for


indicating how often a task should be done.





When an task/job is done, an equivalent receipt with receiptlines is


generated.





When a new job/task should be suggested, it has to calculate the


shortest next intervallength for each repeating orderline. This


intervallength must then be added to the date of the last receiptline.





I want to to this all in a SPROC. My result should be the one next


orderline that should be carried out with a date for when this is.





RESULT


OrderID TaskID TaskDesct EstDate


5000 GETGROCERIES Pick up groceries for miss Lama 19.12.2003





************************************************************************


Is this possible to do all in a SPROC and not having to do any in .NET


(which this is developed in), and can you help me with some ideas?


************************************************************************





To se my starting point, se the diagram at:


http://www.promotion.no/div/diagram.gif





My mind is stuck and this is so far I've reached:





ALTER PROCEDURE dbo.GetNextOrders


AS





CREATE TABLE #Interval


(


ivID int IDENTITY(1,1),


ivTaskID char(20),


ivTaskDescr nvarchar(200),


ivHour int


)





INSERT INTO #TemporaryTable





SELECT RepID, RepIntName


FROM RepeatInterval








SELECT InvoiceDetails.iID, InvoiceDetails.ReceiptID,


InvoiceDetails.OrderID, InvoiceDetails.TaskID,


InvoiceDetails.TaskDescr, InvoiceDetails.DateRegistered


FROM InvoiceDetails LEFT OUTER JOIN


Orders ON InvoiceDetails.OrderID = Orders.OrderID


WHERE (Orders.DateStart < GETDATE()) AND


(Orders.DateEnd > GETDATE() OR Orders.DateEnd IS NULL)