Hi Naomi
The whole thing runs upon submitting an insert/update Order record ( i.e no batch ).
In my other answer i described more easily what i think this should do...
Jaime
>>Hi
>>i have the following tables: for readability i fill it with data so it's easy to understand what's needed
>>Orders Table
>>
>>id date tourid pax ActivityID
>>1 01/04/2012 1 2 1
>>2 01/04/2012 2 1 2
>>3 01/04/2012 1 1 1
>>4 02/04/2012 1 1 3
>>Then i would like to maintain an Activity table, which is the Orders table grouped by Date and TourID as in:
>>Activity table:
>>
>>ActivityID Date tourid TotalOrders TotalPax
>> 1 01/04/2012 1 3 4
>> 2 01/04/2012 2 1 1
>> 3 02/04/2012 1 1 1
>>
>>I would like a stored procedure to do the following: when adding a new Order, either create a new activityID line, or update the quantities if there already is one. When modifying an order, modify the resuting ActivityiD accordingly. Finally, i would like to save the related ActivityID in the orders table.
>>
>>Thanks
>>Jaime
>
>The problem you're presenting can be solved in SQL Server 2008 (and up) using new MERGE command and composable DML. In SQL 2005 I see a problem. Once we change the Activities table, how can we link our changes back to Orders table? If we always will insert/update/delete a single row in Orders table, then this problem can be resolved. But if we allow batch update of orders / batch insert, I don't see a simple solution here.
Why do programs stop working correctly as soon as you leave the Fox?