# Is it possible to do this math in a view?

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:

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.

• Read this: Best approaches for running totals It has several methods that work in older versions, before the 2012 one. Try the cursor if you want the most efficient. An index on `(PersonID, PlanCode) INCLUDE (Deduction)` would help the queries (whichever you choose to use). Commented Sep 21, 2016 at 23:32

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
``````
• Aaaaaaaand I just accidentally awarded the bounty to jyao. I am beginning to think I cannot function without coffee. On a side note, a requirement changed which changed the question enough that I couldn't figure out how to use your code with the new requirement. If you want to take a look, I posted a question on CodeReview. (codereview.stackexchange.com/questions/143899/…) Commented Oct 11, 2016 at 16:06

Something along these lines maybe? You can do running totals with the `OVER` clause for `SUM`.

``````CREATE TABLE Expenses (
expense_id int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
amount decimal(19,5)
)

INSERT INTO Expenses (amount) VALUES (50), (300), (75), (40)
GO

WITH running_total AS (
SELECT
expense_id,
amount,
SUM(amount) OVER (ORDER BY expense_id ROWS UNBOUNDED PRECEDING) AS total
FROM Expenses
)
SELECT
expense_id,
amount,
total,
CASE WHEN total > 400 THEN total - 400 ELSE 0 END AS out_of_pocket_total
FROM running_total
ORDER BY expense_id
``````
• To make this a VIEW, add `CREATE VIEW [ViewName] AS` before the `WITH running_total` line. You'll also need to remove the `ORDER BY` at the end; VIEWs don't support `ORDER BY` in the definition. You can apply the same statement when querying the VIEW. Commented Sep 21, 2016 at 19:09
• ALAS, I am using SqlServer 2008 R2, so anything beyond OVER(Partition By Order By) doesn't work :( Commented Sep 21, 2016 at 22:19
• @Jeff.Clark Bummer! You can probably get similar results by replacing the SUM() with a correlated subquery, at the possible expense of some performance.
– db2
Commented Sep 23, 2016 at 12:11

The following script is strictly based on your sample data (and its table structure) and is runnable for SQL Server 2008.

``````; with c as (
select personid, plancode, deduction
, rownum=ROW_NUMBER() over (partition by PersonID order by personid )
from dbo.Payroll
)
, c2 as (
select personid, plancode, deduction, T.ytd
from c
cross apply (select ytd = sum(deduction)
from c cc
where c.PersonID = cc.PersonID
and c.rownum >= cc.rownum) T(ytd)
)
select personid, plancode, deduction,
OutOfPoket = case when 400 > ytd then 0 else ytd-400 end
from c2
``````

This question can have a simpler way to handle if the table [PayRoll] has a primary key. I put the whole code here:

``````use tempdb
drop table dbo.Payroll
CREATE TABLE Payroll (
id int identity primary key, -- assume there is a PK here
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)
,(1, 'Medical', 111) -- additional row (though not needed as per comments by original owner, but just for fun of adding more complexity)
,(2, 'Medical', 100)
,(2, 'Dental', 150)
,(2, 'Vision', 100)
,(2, 'Disability', 100)
,(2, 'Life', 140)
go

-- easier solution
select personid, plancode, deduction
,OutOfPocket= case when T.ytd > 400 then t.Ytd-400 else 0 end
from dbo.Payroll c
cross apply (
select ytd = sum(deduction)
from dbo.Payroll cc
where c.PersonID = cc.PersonID and c.ID >=cc.ID)  T(ytd)
``````

The result is:

• Oh interesting. The actual data does have a primary key. In my ignorance I did not think it mattered. That distinction will be good for me to remember in the future. I'll keep plugging on implementing these solutions. If you can, keep both the with and without primary key solutions in there so I can compare them for my own learning. Commented Oct 6, 2016 at 21:40