CODEWITHSUNDEEP
sqlsql-server-2008
tempid
tempid

Reputation: 8208

sql query help - trying to get rid of temp tables

I have the following tables -

Resource
--------------------
Id, ProjectId, Hours, ApproverId

Project
--------------------
Id, Name

The input is ApproverId. I need to retrieve all the rows that have matching ApproverId (simple enough). And for every resource that I get back, I also need to get their hours (same table) whose approverId is not the one that is passed in (business requirement, to be grayed out in the UI). What I'm doing right now is - get all resources based on ApproverId, stored them in a temp table, then do a distinct on Resource.Id, store it in a different temp table, and then for every Resource.Id, get the rows where the ApproverId is not the one that is passed. Can I combine it all in a single query instead of using temp tables?

Thanks!

Edit: I'm using SQL Server 2008 R2.

Edit 2: Here's my stored procedure. I have changed the logic slightly after reading the comments. Can we get rid of all temp tables and make it faster -

ALTER PROCEDURE GetResourceDataByApprover
    @ApproverId UNIQUEIDENTIFIER    
AS 
    CREATE TABLE #Table1
        (
          Id SMALLINT PRIMARY KEY
                      IDENTITY(1, 1) ,
          ResourceId UNIQUEIDENTIFIER
        )        

    CREATE TABLE #Table2
        (
          ResourceId UNIQUEIDENTIFIER ,
          ProjectId UNIQUEIDENTIFIER ,
          ProjectName NVARCHAR(1024)          
        )

    INSERT  INTO #Table1
            SELECT DISTINCT
                    ResourceId
            FROM    dbo.Resource T
            WHERE   T.ApproverId = @ApproverId


    DECLARE @i INT 
    DECLARE @numrows INT
    DECLARE @resourceId UNIQUEIDENTIFIER

    SET @i = 1 
    SET @numrows = ( SELECT COUNT(*)
                     FROM   #Table1
                   )

    IF @numrows > 0 
        WHILE ( @i <= ( SELECT  MAX(Id)
                        FROM    #Table1
                      ) ) 
            BEGIN 
                SET @resourceId = ( SELECT  ResourceId
                                    FROM    #Table1
                                    WHERE   Id = @i
                                  )


                INSERT  INTO #Table2
                        SELECT 
                                T.ResourceId ,
                                T.ProjectId ,
                                P.Name AS ProjectName

                        FROM    dbo.[Resource] T
                                INNER JOIN dbo.Project P ON T.ProjectId = P.ProjectId                                
                        WHERE   T.ResourceId = @resourceId                               


                SET @i = @i + 1 
            END

    SELECT  *
    FROM    #Table1 
    SELECT  *
    FROM    #Table2

    DROP TABLE #Table1  
    DROP TABLE #Table2

Upvotes: 0

Views: 153

Answers (2)

k06a
k06a

Reputation: 18775

Do you want to know how concrete Approver wastes his time?

SELECT p.Id, p.Name, SUM(r.Hours) as TotalHours
FROM Resource r
LEFT JOIN Project p
ON r.ProjectId = p.Id
WHERE ApproverId = %ConcreteApproverId%
GROUP BY p.Id, p.Name
HAVING SUM(r.Hours) > 0

This query will produce this table example:

+-----+----------+-------+
| Id  | Project  | Hours |
+-----+----------+-------+
| 203 | ProjectA | 25    |
| 202 | ProjectB | 34    |
| 200 | ProjectC | 46    |
+-----+----------+-------+

Upvotes: 0

erikxiv
erikxiv

Reputation: 4075

This query should return two rows for every resource, one for the specified approver and one for all other approvers.

SELECT 
  Id, 
  CASE
    WHEN ApproverId=@approverId THEN 'SpecifiedApprover'
    ELSE 'OtherApprover'
  END AS Approver, 
  SUM(Hours) AS Hours
FROM Resource
GROUP BY 
  Id,
  CASE
    WHEN ApproverId=@approverId THEN 'SpecifiedApprover'
    ELSE 'OtherApprover'
   END

Upvotes: 1

Related Questions