Is There Any Easier Way Than Using This Cursor?

Hello,

I just finished a lengthy process (for me) of writing this cursor that gets each row from the Fact table, and creates a running total of the billable hours (hours from rows where BillableType = 1). It starts over when the month changes, the year changes, or the employee changes.

I did this again for a running total of billable hours for the year.

I put these in 2 stored procedures and run them in an Execute SQL control flow task in my SSIS package. It seems like SSIS is designed to make this kind of procedure simpler, and I'm wondering if I'm doing this in the best way, or if there's a more efficient way to do this using tasks inside SSIS. Can anyone advise? Any help is greatly appreciated.

Best,
Andy




Don't feel obligated to read this cursor if you understand the problem above. It's not color coded because when I copied it from SQL I lost the tabbing on the case expressions.





Code Snippet

CREATE PROCEDURE YearTotalHours AS

DECLARE @FactBillingId Int;
DECLARE @dt DateTime;
DECLARE @EmpKey Int;
DECLARE @YearTotalHours Float;
DECLARE @NewYearTotalHours Float;
DECLARE @NewYear Int;
DECLARE @BillableType Int;


DECLARE FactBillingFinalRows CURSOR FOR
SELECT FactBillingId
FROM FactBillingFinal

OPEN FactBillingFinalRows

FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId

WHILE @@FETCH_STATUS = 0
BEGIN




SET @EmpKey = (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @dt = (SELECT dt FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
SET @BillableType = (SELECT BillableTypeKey FROM FactBillingFinal WHERE FactbillingId = @FactBillingId)
SET @NewYear = (SELECT Year(@dt))
SET @NewYearTotalHours =


CASE
WHEN @FactBillingId = 1 THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN @EmpKey <> (SELECT EmployeeKey FROM FactBillingFinal WHERE FactBillingId = @FactBillingId - 1)
THEN
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
ELSE
CASE
WHEN YEAR(@dt) = (SELECT YEAR(dt) FROM FactBillingFinal WHERE FactBillingId = @FactbillingId - 1)
THEN
CASE
WHEN @BillableType = 1
THEN (SELECT @YearTotalHours + (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId))
ELSE
CASE
WHEN @NewYearTotalHours IS NULL
THEN 0
ELSE @NewYearTotalHours
END
END
ELSE
CASE
WHEN @BillableType = 1 THEN (SELECT Hours FROM FactBillingFinal WHERE FactBillingId = @FactBillingId)
ELSE 0
END
END


END
END



UPDATE FactBillingFinal SET YearTotalHours = @NewYearTotalHours WHERE FactBillingId = @FactBillingId

SET @YearTotalHours = @NewYearTotalHours;

FETCH NEXT FROM FactBillingFinalRows
INTO @FactBillingId
END

CLOSE FactBillingFinalRows
DEALLOCATE FactBillingFinalRows