Alright, first you'll need to grab a barf bag. I've been tasked with optimizing several old stored procedures in our database. This SP does the following:

1) cursor loops through a series of "buildings"

2) cursor loops through a week, Sunday-Saturday

3) has a huge set of IF blocks that are responsible for counting how many Objects of what Types are present in a given building

Essentially what you'll see in this code block is that, if there are 5 objects of type #2, it will increment @Type_2_Objects_5 by 1.

    IF @Number_Type_1_Objects = 0
    BEGIN
        SET @Type_1_Objects_0 = @Type_1_Objects_0 + 1
    END
    IF @Number_Type_1_Objects = 1
    BEGIN
        SET @Type_1_Objects_1 = @Type_1_Objects_1 + 1
    END
    IF @Number_Type_1_Objects = 2
    BEGIN
        SET @Type_1_Objects_2 = @Type_1_Objects_2 + 1
    END
    IF @Number_Type_1_Objects = 3
    BEGIN
        SET @Type_1_Objects_3 = @Type_1_Objects_3 + 1
    END
[... Objects_4 through Objects_20 for Type_1]

IF @Number_Type_2_Objects = 0
    BEGIN
        SET @Type_2_Objects_0 = @Type_2_Objects_0 + 1
    END
    IF @Number_Type_2_Objects = 1
    BEGIN
        SET @Type_2_Objects_1 = @Type_2_Objects_1 + 1
    END
    IF @Number_Type_2_Objects = 2
    BEGIN
        SET @Type_2_Objects_2 = @Type_2_Objects_2 + 1
    END
    IF @Number_Type_2_Objects = 3
    BEGIN
        SET @Type_2_Objects_3 = @Type_2_Objects_3 + 1
    END
[... Objects_4 through Objects_20 for Type_2]

In addition to being extremely hacky (and limited to a quantity of 20 objects), it seems like a terrible way of handling this. In a traditional language, this could easily be solved with a 2-dimensional array...

objects[type][quantity] += 1;

I'm a T-SQL novice, but since writing stored procedures often uses a lot of temporary tables (which could essentially be a 2-dimensional array) I was wondering if someone could illuminate a better way of handling a situation like this with two dynamic pieces of data to store.

Requested in comments: The columns are simply Number_Type_1_Objects, Number_Type_2_Objects, Number_Type_3_Objects, Number_Type_4_Objects, Number_Type_5_Objects, and CurrentDateTime. Each row in the table represents 5 minutes.

The expected output is to figure out what percentage of time a given quantity of objects is present throughout each day.

Sunday - Object Type 1 0 objects - 69 rows, 5:45, 34.85% 1 object - 85 rows, 7:05, 42.93% 2 objects - 44 rows, 3:40, 22.22%

On Sunday, there were 0 objects of type 1 for 34.85% of the day. There was 1 object for 42.93% of the day, and 2 objects for 22.22% of the day. Repeat for each object type.