7

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.

  • 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). – ypercubeᵀᴹ Sep 21 '16 at 23:32
2

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/…) – Jeff.Clark Oct 11 '16 at 16:06
6

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. – Steven Hibble Sep 21 '16 at 19:09
  • ALAS, I am using SqlServer 2008 R2, so anything beyond OVER(Partition By Order By) doesn't work :( – Jeff.Clark Sep 21 '16 at 22:19
  • 1
    @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 Sep 23 '16 at 12:11
5
+50

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 

enter image description here

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: enter image description here

  • 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. – Jeff.Clark Oct 6 '16 at 21:40

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.