2
\$\begingroup\$

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 (OutOfPocket).

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
\$\endgroup\$
  • \$\begingroup\$ What sql-server version are you using? \$\endgroup\$ – mxix Oct 11 '16 at 17:37
  • \$\begingroup\$ @mxix Sorry, I am using 2008 R2 \$\endgroup\$ – Jeff.Clark Oct 11 '16 at 21:32
3
\$\begingroup\$

I think this will work you have to use an inline select statement to get the running total, essentially this is what SUM() OVER function does in 2012+ except the engine was designed to handle the function faster.

WITH C AS (
    SELECT EmployeeID
            ,PlanCode
            ,IssCost
            ,(SELECT SUM(IssCost) FROM Payroll as b WHERE a.EmployeeID = b.EmployeeID AND a.PlanCode >= b.plancode) as YTD
    FROM Payroll as a   
)
SELECT  EmployeeID
    ,PlanCode
    ,IssCost
    ,YTD
    ,(YTD - 400 + abs(IssCost - 400))/2 AS OutOfPocket
FROM C
ORDER BY EmployeeID, PlanCode
|improve this answer|||||
\$\endgroup\$
1
\$\begingroup\$

If you are running sqlsever 2012+. You can do it easly with window functions.

select
    *
into #Payroll
from (
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) 
) x (EmployeeID, PlanCode, IssCost)

select
*,
CASE 
    WHEN ytd < 400 
        THEN 0 
    WHEN (ytd > 400) AND (ytd < (400 + IssCost)) 
        THEN ytd - 400
    ELSE IssCost
END OutOfPocket
from (
    select
    *,
    SUM(IssCost) OVER (
            PARTITION BY EmployeeID
            ORDER BY PlanCode
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) ytd
    from #Payroll
) t

drop table #Payroll
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Ack, I forgot to mention that I am on 2008R2. I mentioned in the linked question but neglected to transfer it here. \$\endgroup\$ – Jeff.Clark Oct 11 '16 at 20:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.