I have a view that performs some rolling totals. In this instance, it must be in a view. The issue is that it takes about 10 minutes to run, returning 2700 records. There should be a faster way, but I am not privy at the moment. I asked a question here in order to get this far.
I am running SqlServer 2008 R2, so advanced over() functionality doesn't work, just Partition by Order by)
The difference between my question and what code I have below is that my requirements changed slightly, requiring that the
OutOfPocket not be a total, but rather simply reflect the amount of the deduction (
IssCost) once the $400 is exhausted. The only exception is on the deduction that exhausts the $400, the leftover is displayed, not the full deduction. A
CROSS APPLY was used to achieve this effect. I couldn't quite figure out how to achieve the new requirement based upon the answer I selected in my question linked above, so I used a different answer that worked.
The concept of the view is that someone starts with $400 to spend. Every deduction (
IssCost in the code), deducts from the initial $400. If a person starts going over the $400, the money will come from their own pocket (
Some sample data and the view code are as follows:
CREATE TABLE Payroll ( EmployeeID int, PlanCode varchar(10), IssCost int NULL ) GO INSERT INTO Payroll (EmployeeID, PlanCode, IssCost) VALUES (1, 'Medical', 200) ,(1, 'Dental', 250) ,(1, 'Vision', 300) ,(2, 'Medical', 100) ,(2, 'Dental', 150) ,(2, 'Vision', 100) ,(2, 'Disability', 100) ,(2, 'Life', 140) --View starts here WITH C AS ( SELECT EmployeeID ,PlanCode ,IssCost ,rownum = ROW_NUMBER() over (Partition BY EmployeeID Order by PlanCode) FROM Payroll ), C2 AS ( SELECT EmployeeID ,PlanCode ,IssCost ,T.Ytd FROM C CROSS APPLY (SELECT ytd = SUM(IssCost) FROM C CC WHERE C.EmployeeID = CC.EmployeeID AND C.rownum >= CC.rownum) T(ytd)) SELECT EmployeeID ,PlanCode ,IssCost ,OutOfPocket = CASE WHEN ytd < 400 THEN 0 WHEN (ytd > 400) AND (ytd < (400 + IssCost)) THEN ytd - 400 ELSE IssCost END FROM C2