Help with a query
- by stackoverflowuser
Hi
Based on the following table
ID Effort Name
-------------------------
1 1 A
2 1 A
3 8 A
4 10 B
5 4 B
6 1 B
7 10 C
8 3 C
9 30 C
I want to check if the total effort against a name is less than 40 then add a row with effort = 40 - (Total Effort) for the name. The ID of the new row can be anything. If the total effort is greater than 40 then trucate the data for one of the rows to make it 40.
So after applying the logic above table will be
ID Effort Name
-------------------------
1 1 A
2 1 A
3 8 A
10 30 A
4 10 B
5 4 B
6 1 B
11 25 B
7 10 C
8 3 C
9 27 C
I was thinking of opening a cursor, keeping a counter of the total effort, and based on the logic insert existing and new rows in another temporary table.
I am not sure if this is an efficient way to deal with this. I would like to learn if there is a better way.
