January 18, 2003 at 8:56 pm
Guys, the code below returns a bunch of result sets and I need it to return one result set. Just in case my strategy is incorrect let me tell you what I am trying to accomplish.
I have a table named [Time] with columns named resourceID (guid), ProjectID (guid), RegularHours (numeric) just to name a few. In essence it records employee time entries. Sample data would be like
Name Project Hours
-------------------------------
Bob Smith A23 12
Frank Wilson A23 7
Bob Smith A23 8
Bob Smith A23 3
Frank Wilson A23 1
I need to be able to create a project report that shows the employees names and the total hours worked on the given project. Like this:
Name Total Hours
---------------------------
Bob Smith 23
Frank Wilson 8
Here is the code:
---------------------------------------------------------------------------
DECLARE @resourceName varchar(100)
DECLARE @TotalHours int
DECLARE @resourceID uniqueidentifier
DECLARE EmpsCursor cursor for
SELECT DISTINCT Time.resourceID
FROM Time WHERE ProjectID = 'cbd8132c-3af0-4b32-b117-d327ad9ddfdc'
ORDER BY resourceID
OPEN EmpsCursor
FETCH NEXT FROM EmpsCursor INTO
@resourceID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @TotalHours = sum(Time.regularhours) FROM Time
WHERE ProjectID = 'cbd8132c-3af0-4b32-b117-d327ad9ddfdc'
AND ResourceID = @resourceID
SELECT distinct Time.resourceID, Resource.Name, @TotalHours AS TotalHours
FROM Time
INNER JOIN Resource ON Time.ResourceID = Resource.ResourceID
WHERE ProjectID = 'cbd8132c-3af0-4b32-b117-d327ad9ddfdc' AND Time.resourceID = @resourceID
FETCH NEXT FROM EmpsCursor
INTO @resourceID
END
CLOSE EmpsCursor
DEALLOCATE EmpsCursor
GO
---------------------------------------------------------------------
This returns the correct data, but it does so in multiple result sets, I need it to return it as if I just did a normal select.
Thanks in advance for any help
Todd Oliver
January 19, 2003 at 6:34 am
Ok, just going a little extreme here in your work. Wat you need is to SUM the hours and group by the person and project. Like so...
SELECT [Name], Project, SUM(Hours) AS Total_Hours FROM [Time] GROUP BY [Name], Project
Or drop project and use a where clause if you only need a specific project.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply