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 isgenerated.When a new job/task should be suggested, it has to calculate theshortest next intervallength for each repeating orderline. Thisintervallength 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 nextorderline that should be carried out with a date for when this is.RESULTALTER PROCEDURE dbo.GetNextOrdersASCREATE 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)
This is the result I want...OrderID TaskID TaskDesct EstDate5000 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.gifMy mind is stuck and this is so far I've reached:
Estimate new time in ordersystem
Posted - 2003-12-04 : 10:49:03
