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)
It may make sense to think of the
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
Based upon our constraints and a 3rd column that was not necessary to include, there will not be any duplicate entries possible.
(PersonID, PlanCode) INCLUDE (Deduction)would help the queries (whichever you choose to use).