Help with a query

Question

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.

Answer 1

This will give you the names that need modify:

SELECT Name, SUM(Effort)
FROM Table
GROUP BY Name
HAVING SUM(Effort) < 40

Select this into a temp table, Add a column for 40 - SUM, then create an insert statement from that. Much better than a cursor.

Answer 2

I think the first part could be done this way:

 INSERT INTO tbl(Effort, Name)
 SELECT 40 - SUM(Effort), Name
 FROM tbl
 GROUP BY Name
 HAVING SUM(Effort) < 40) 

The second part is harder. Perhaps you could do something like this instead?

 INSERT INTO tbl(Effort, Name)
 SELECT 40 - SUM(Effort), Name
 FROM tbl
 GROUP BY Name
 HAVING SUM(Effort) <> 40) 

What this does is, rather than making changes to your actual data, adds a row with a negative number for the Name if the total effort is > 40 hours, or a positive value if it is < 40 hours. This seems much safer for your data integrity than messing with the original values.

Answer 3

This will do the first part:

Insert Into dbo.Test (Name, Effort)
Select t.Name, 40 - SUM(t.Effort)
From dbo.Test t
Group By t.Name
Having SUM(t.Effort) < 40

And this will do the second part:

Update a
Set a.Effort = a.Effort - b.AmountToDeduct
From dbo.Test a
Join (
         Select t.Name, (40 - SUM(t.Effort)) as 'AmountToDeduct'
         From dbo.Test t
         Group By t.Name
         Having SUM(t.Effort) > 40
     )b on a.Name = b.Name
Where a.ID = (Select MAX(c.ID) 
              From dbo.Test c   
          Where c.Name = a.Name
        )

Answer 4

In SQL Server 2008, this may be done with a single MERGE statement:

DECLARE @efforts TABLE (id INT NOT NULL PRIMARY KEY, effort INT NOT NULL, name CHAR(1))

INSERT
INTO    @efforts
VALUES  (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'),
        (10, 60, 'C')

SELECT  *
FROM    @efforts
ORDER BY
        name, id

;WITH    total AS
        (       SELECT  *
                FROM    @efforts e
                UNION ALL
                SELECT  ROW_NUMBER() OVER(ORDER BY name) +
                        (
                        SELECT  MAX(id)
                        FROM    @efforts
                        ),
                        40 - SUM(effort),
                        name
                FROM    @efforts
                GROUP BY
                        name
                HAVING  SUM(effort) < 40
        ),
        source AS
        (
        SELECT  *,
                (
                SELECT  SUM(effort)
                FROM    total ep
                WHERE   ep.name = e.name
                        AND ep.id <= e.id
                ) AS ce,
                COALESCE(
                (
                SELECT  SUM(effort)
                FROM    total ep
                WHERE   ep.name = e.name
                        AND ep.id < e.id
                ), 0) AS cp
        FROM    total e
        )
MERGE
INTO    @efforts e
USING   source s
ON      e.id = s.id
WHEN MATCHED AND 40 BETWEEN cp AND ce THEN
UPDATE
SET     e.effort = s.effort + 40 - ce
WHEN MATCHED AND cp > 40 THEN
DELETE
WHEN NOT MATCHED BY TARGET THEN
INSERT  (id, effort, name)
VALUES  (id, effort, name);

SELECT  *
FROM    @efforts
ORDER BY
        name, id

In SQL Server 2005, you'll need two statements (in one transaction):

DECLARE @efforts TABLE (id INT NOT NULL PRIMARY KEY, effort INT NOT NULL, name CHAR(1))

INSERT
INTO    @efforts
VALUES  (1, 1, 'A')

INSERT
INTO    @efforts
VALUES  (2, 1, 'A')
INSERT
INTO    @efforts
VALUES  (3, 8, 'A')
INSERT
INTO    @efforts
VALUES  (4, 10, 'B')
INSERT
INTO    @efforts
VALUES  (5, 4, 'B')
INSERT
INTO    @efforts
VALUES  (6, 1, 'B')
INSERT
INTO    @efforts
VALUES  (7, 10, 'C')
INSERT
INTO    @efforts
VALUES  (8, 3, 'C')
INSERT
INTO    @efforts
VALUES  (9, 30, 'C')
INSERT
INTO    @efforts
VALUES  (10, 60, 'C')

;WITH    total AS
        (
        SELECT  *,
                COALESCE(
                (
                SELECT  SUM(effort)
                FROM    @efforts ep
                WHERE   ep.name = e.name
                        AND ep.id <= e.id
                ), 0) AS cp
        FROM    @efforts e
        )
DELETE
FROM    total
WHERE   cp > 40

INSERT
INTO    @efforts
SELECT  (
        SELECT  MAX(id)
        FROM    @efforts
        ) +
        ROW_NUMBER() OVER (ORDER BY name),
        40 - SUM(effort),
        name
FROM    @efforts
GROUP BY
        name
HAVING  SUM(effort) < 40

SELECT  *
FROM    @efforts
ORDER BY
        name, id