<p>I have a view that has a list of jobs in it, with data like who they're assigned to and the stage they are in. I need to write a stored procedure that returns how many jobs each person has at each stage.</p>

<p>So far I have this (simplified):</p>

<pre><code>DECLARE @ResultTable table 
(
  StaffName nvarchar(100),
  Stage1Count int,
  Stage2Count int
)

INSERT INTO @ResultTable (StaffName, Stage1Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage1 = 1
  GROUP BY StaffName

INSERT INTO @ResultTable (StaffName, Stage2Count)
  SELECT StaffName, COUNT(*) FROM ViewJob
  WHERE InStage2 = 1
  GROUP BY StaffName
</code></pre>

<p>The problem with that is that the rows don't combine. So if a staff member has jobs in stage1 and stage2 there's two rows in @ResultTable. What I would really like to do is to update the row if one exists for the staff member and insert a new row if one doesn't exist.</p>

<p>Does anyone know how to do this, or can suggest a different approach?
I would really like to avoid using cursors to iterate on the list of users (but that's my fall back option).</p>

<p>I'm using SQL Server 2005.</p>

<p><strong>Edit: @Lee:</strong> Unfortunately the InStage1 = 1 was a simplification. It's really more like WHERE DateStarted IS NOT NULL and DateFinished IS NULL.</p>

<p><strong>Edit: @BCS:</strong> I like the idea of doing an insert of all the staff first so I just have to do an update every time. But I'm struggling to get those UPDATE statements correct.</p>

[{'answer_id': 19097, 'author': 'AlexCuse', 'author_id': 794, 'author_profile': 'https://Stackoverflow.com/users/794', 'pm_score': 1, 'selected': False, 'text': '<p>To get a real "upsert" type of query you need to use an if exists... type of thing, and this unfortunately means using a cursor.</p>\n\n<p>However, you could run two queries, one to do your updates where there is an existing row, then afterwards insert the new one.  I\'d think this set-based approach would be preferable unless you\'re dealing exclusively with small numbers of rows.</p>\n'}, {'answer_id': 19098, 'author': 'Ryan Farley', 'author_id': 1627, 'author_profile': 'https://Stackoverflow.com/users/1627', 'pm_score': 2, 'selected': False, 'text': "<p>You could just check for existence and use the appropriate command. I believe this really does use a cursor behind the scenes, but it's the best you'll likely get:  </p>\n\n<pre><code>IF (EXISTS (SELECT * FROM MyTable WHERE StaffName = @StaffName))\nbegin\n    UPDATE MyTable SET ... WHERE StaffName = @StaffName\nend\nelse\nbegin\n    INSERT MyTable ...\nend \n</code></pre>\n\n<p>SQL2008 has a new MERGE capability which is cool, but it's not in 2005.</p>\n"}, {'answer_id': 19099, 'author': 'Lee', 'author_id': 1954, 'author_profile': 'https://Stackoverflow.com/users/1954', 'pm_score': 2, 'selected': False, 'text': "<p>Actually, I think you're making it much harder than it is.  Won't this code work for what you're trying to do?</p>\n\n<pre><code>SELECT StaffName, SUM(InStage1) AS 'JobsAtStage1', SUM(InStage2) AS 'JobsAtStage2'\n  FROM ViewJob\nGROUP BY StaffName\n</code></pre>\n"}, {'answer_id': 19105, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 0, 'selected': False, 'text': "<p>The following query on your result table should combine the rows again. This is assuming that InStage1 and InStage2 are never both '1'.</p>\n\n<pre><code>select distinct(rt1.StaffName), rt2.Stage1Count, rt3.Stage2Count\nfrom @ResultTable rt1\nleft join @ResultTable rt2 on rt1.StaffName=rt2.StaffName and rt2.Stage1Count is not null\nleft join @ResultTable rt3 on rt1.StaffName=rt2.StaffName and rt3.Stage2Count is not null\n</code></pre>\n"}, {'answer_id': 19109, 'author': 'BCS', 'author_id': 1343, 'author_profile': 'https://Stackoverflow.com/users/1343', 'pm_score': 2, 'selected': True, 'text': '<p>IIRC there is some sort of "On Duplicate" (name might be wrong) syntax that lets you update if a row exists (MySQL)</p>\n\n<p>Alternately some form of:</p>\n\n<pre><code>INSERT INTO @ResultTable (StaffName, Stage1Count, Stage2Count)\n  SELECT StaffName,0,0 FROM ViewJob\n  GROUP BY StaffName\n\nUPDATE @ResultTable Stage1Count= (\n  SELECT COUNT(*) AS count FROM ViewJob\n  WHERE InStage1 = 1\n  @ResultTable.StaffName = StaffName)\n\nUPDATE @ResultTable Stage2Count= (\n  SELECT COUNT(*) AS count FROM ViewJob\n  WHERE InStage2 = 1\n  @ResultTable.StaffName = StaffName)\n</code></pre>\n'}, {'answer_id': 19198, 'author': 'Ray', 'author_id': 233, 'author_profile': 'https://Stackoverflow.com/users/233', 'pm_score': 0, 'selected': False, 'text': "<p>I managed to get it working with a variation of BCS's answer. It wouldn't let me use a table variable though, so I had to make a temp table.</p>\n\n<pre><code>CREATE TABLE #ResultTable\n(\n  StaffName nvarchar(100),\n  Stage1Count int,\n  Stage2Count int\n)\n\nINSERT INTO #ResultTable (StaffName)\n  SELECT StaffName FROM ViewJob\n  GROUP BY StaffName\n\nUPDATE #ResultTable SET \n  Stage1Count= (\n    SELECT COUNT(*) FROM ViewJob V\n    WHERE InStage1 = 1 AND \n        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS\n    GROUP BY V.StaffName),\n  Stage2Count= (\n    SELECT COUNT(*) FROM ViewJob V\n    WHERE InStage2 = 1 AND \n        V.StaffName = @ResultTable.StaffName COLLATE Latin1_General_CI_AS\n    GROUP BY V.StaffName)\n\nSELECT StaffName, Stage1Count, Stage2Count FROM #ResultTable\n\nDROP TABLE #ResultTable\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/19089', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/233/']