Help with a query

Posted by stackoverflowuser on Stack Overflow See other posts from Stack Overflow or by stackoverflowuser
Published on 2010-04-15T18:28:23Z Indexed on 2010/04/15 18:33 UTC
Read the original article Hit count: 210

Filed under:
|
|
|

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.

© Stack Overflow or respective owner

Related posts about tsql

Related posts about sql