>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.