SQL Server – How to Perform Math in a View

running-totalssql serversql-server-2008-r2t-sqlview

I have been tasked with creating a view for a client. Specifically it must be in a view. However, there is some math that I am not sure how to do within a view. I do not know if it is even possible. But then again, my mind is feeble.

I am using SQL Server 2008R2, so advanced OVER() functionality doesn't work.

Let's say a person is given $400 to spend. They can spend more, but the first $400 is free. One column of the report will have the amount the person spent on something, and another will have the total amount the person needs to pay out of their own pocket.

So, for the first record in the report for this person, one column will have an amount they have spent, say $50, and then a second column will have a $0. Behind the scenes they still have $350 to spend.

The next record has the person spending $300. The second column will still show a $0, and behind the scenes the initial $400 is now $50.

The third record for the person shows they spent $75 dollars, but they only have $50 left over from the initial $400. The second column should now have a $25 value in it. They have exhausted the initial $400 and are now spending their own money.

The fourth record shows they spent $40, so now the second column will show a $65. etc…

I have briefly read about CTEs and table valued functions and such, but is it possible to use them in any combination to give the desired behavior above?

Below is some sample code for structure and desired results

CREATE TABLE Payroll (
    PersonID int,
    PlanCode varchar(10),
    Deduction int NULL
)
GO

INSERT INTO Payroll (PersonID, PlanCode, Deduction)
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)

Desired results:

enter image description here

It may make sense to think of the OutOfPocket as TotalOutOfPocket.

There is nothing like a timestamp in the source data for ordering of the entries. The ordering is not too important. If any ordering is done, it will be on the PlanCode.

Based upon our constraints and a 3rd column that was not necessary to include, there will not be any duplicate entries possible.

Best Answer

this sorts on PlanCode - if you have duplicates then use a row_number()
if you need a particular order then you need to have that order in the table 

  select PersonID, PlanCode, Deduction, [sum]
       , case when [sum] < 400 then 0 else [sum] - 400 end as oop
  from
  ( select p1.PersonID, p1.PlanCode, p1.Deduction 
         , ( select sum(p2.Deduction) 
               from payroll p2  
              where p2.PersonID  = p1.PersonID 
                and p2.PlanCode <= p1.PlanCode ) as [sum] 
    from payroll p1 
  ) tt
  order by tt.PersonID, tt.PlanCode

or 

  select p1.PersonID, p1.PlanCode, p1.Deduction 
       , case  when sum(p2.Deduction) < 400 then 0 else sum(p2.Deduction) - 400 end as OOP
  from payroll p1 
  join payroll p2  
        on p2.PersonID  = p1.PersonID 
       and p2.PlanCode <= p1.PlanCode  
 group by p1.PersonID, p1.PlanCode, p1.Deduction
Related Question