CODEWITHSUNDEEP
sql-serversql-server-2005dynamiccursor
DoStuffZ
DoStuffZ

Reputation: 800

SQL Cursor in dynamic content

I need a little help wrapping my head around CURSOR.

I made an aspx report, it was quite simple. For each project I needed to see how many hours went on Research, Development and Internal. That first one was quite simple, I just hardcoded the solution with 3 groups and an equal 3 types of time. Boss thought that was a brilliant report so he wanted it to cover all customers. Unfortunately the next customer had 5 groups and each group had 2-7 different types of time to each.

Now I need a dynamic solution where I can specify the groups (done) and specify which types of time goes into each group (done), last I need to combine those two with the table that record hours spent.

I have a basic cursor where I can list the projects, the type of time and how much time was spent within this group of time.

What I have is:
Project A -|- Group A -|- 5
Project B -|- Group A -|- 2
Project C -|- Group A -|- 10

Project A -|- Group B -|- 1
Project B -|- Group B -|- 10
Project C -|- Group B -|- 2

Project A -|- Group C -|- 0
Project B -|- Group C -|- 3
Project C -|- Group C -|- 7

Problem is, I need the report to be
Header____Group A|Group B|Group C|Group N
Project A -|- 5 -|- 1 -|- 0 -|- x
Project B -|- 2 -|- 10 -|- 3 -|- y
Project C -|- 10 -|- 2 -|- 7 -|- z 

DECLARE @iTimeTypeGroupID int

DECLARE cur CURSOR LOCAL READ_ONLY
FOR 
SELECT iRefTimeTypeGroupID
FROM tbl_TimeTypeGrouping
WHERE iRefCustomerID = @customerID

OPEN cur

FETCH NEXT FROM cur
INTO @iTimeTypeGroupID

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT PT.iRefProjectID
        , PT.iRefTimeTypeID
        , SUM(PT.decNumberOfHours) sumNumberOfHours
    FROM tbl_ProjectTransaction PT
    WHERE iRefTimeTypeID IN (
        SELECT iRefTimeTypeID 
        FROM tbl_TimeTypeGrouping 
        WHERE iRefTimeTypeGroupID = @iTimeTypeGroupID
            AND iRefCustomerID = @customerID)
    GROUP BY PT.iRefProjectID
        , PT.iRefTimeTypeID

    FETCH NEXT FROM cur
    INTO @iTimeTypeGroupID
END

CLOSE cur
DEALLOCATE cur

Upvotes: 3

Views: 1021

Answers (1)

Arion
Arion

Reputation: 31249

I am not sure about the column names. So in this example I expect the tbl_ProjectTransaction to have a column called projectName and tbl_TimeTypeGrouping to have a GroupName. Like someone commeted in the question you should not use a cursor in this case. You should use a dynamic pivot. Here is and example:

Get the names of the groups like this:

DECLARE @cols VARCHAR(MAX)
SELECT @cols=STUFF
(
    (
        SELECT 
            ',' +QUOTENAME(tbl_TimeTypeGrouping.sGroupName) -- ????
        FROM
            tbl_TimeTypeGrouping
        FOR XML PATH('')
    )
,1,1,'')

This will give you:

'[Group A],[Group B],[Group C],[Group N]'

And then do a dynamic pivot like this:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        tbl_ProjectTransaction.sProjectName, -- ????
        tbl_ProjectTransaction.decNumberOfHours,
        tbl_TimeTypeGrouping.sGroupName -- ???
    FROM
        tbl_ProjectTransaction
        JOIN tbl_TimeTypeGrouping
            ON tbl_ProjectTransaction.iRefTimeTypeID=tbl_TimeTypeGrouping.iRefTimeTypeID
) AS SourceTable
PIVOT
(
    SUM(decNumberOfHours)
    FOR GroupName IN ('+@cols+')
) As Pvt'
EXECUTE(@query)

References:

Upvotes: 2

Related Questions