>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
I'm not sure I understand. Do you want to maintain 1 Activity line for each date for the same TourId? Also, it's a bit complicated relationship . Also, what happens if you change Order date?
In any case, it's not hard to write a stored procedure that will fit your requirements (if I understood activity table correctly - 1 row per date/TourId).
What is your SQL Server version?
If it's not broken, fix it until it is.
My Blog