Error on SQL script

Jul 3, 2007 at 11:33 PM
Had an error install RC 2.0 in the SQL script to create the sp
The Join to CORPTFS.TfsWorkItemTracking.dbo.WorkItemsLatest WorkItems was errorring removed the reference to CORPTFS
this procedure executed and created the SP.

CREATE PROCEDURE dbo.usp_RS_Problems
@sid nvarchar(128)
AS
BEGIN
DECLARE @problemWorkItems TABLE(workItemId int, reportedHours decimal(18,4), allocatedHours decimal(18,4))

INSERT INTO @problemWorkItems
SELECT Totals.workItemId, Totals.totalHours AS reportedHours, SUM(Entries.hours) AS allocatedHours
FROM WorkItemTotals Totals WITH(NOLOCK)
JOIN TimesheetEntries Entries WITH(NOLOCK)
ON Totals.workItemId = Entries.workItemId
WHERE Entries.sid = @sid
AND Totals.sid = @sid
GROUP BY Totals.workItemId, Totals.totalHours
HAVING Totals.totalHours != SUM(Entries.hours)

INSERT INTO @problemWorkItems
SELECT Entries.workItemId, 0 AS reportedHours, SUM(Entries.hours) AS allocatedHours
FROM TimesheetEntries Entries WITH(NOLOCK)
LEFT JOIN WorkItemTotals Totals WITH(NOLOCK)
ON Totals.workItemId = Entries.workItemId
WHERE Totals.workItemId IS NULL
AND Entries.sid = @sid
GROUP BY Entries.workItemId
HAVING SUM(Entries.hours) != 0

SELECT Problems.workItemId, WorkItems.Title AS title, WorkItems.Work Item Type AS workItemType, Problems.reportedHours, Problems.allocatedHours
FROM @problemWorkItems Problems
JOIN TfsWorkItemTracking.dbo.WorkItemsLatest WorkItems
ON Problems.workItemId = WorkItems.ID
END
GO
Coordinator
Jul 4, 2007 at 6:25 PM
Nice catch, thanks. I've fixed the database setup script and have created new releases for the TeamPlain 1.x and 2.x versions.