Is it possible to do this math in a view?
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
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:
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