recurring balance?
in [Programming SQL]
|
From: SF on 22 Mar 2010 00:10 Hi, I have a table below for tracking the Education Allowance for each employee. Each employee will receive an annual allowance of USD600. If the employee made the first request of 200, the remaining balance would be 400. When the employee request second request of 150 then the balance would be 600 - (200 + 150) My problem is that I cannot find a way to update the remaining balance to reflect the actual one becauase the RemainingBalance is a recurring balance. I though that store procedure would be good for the job but I don't know how to do that? Could someone advice? ClaimID EmpID ClaimDate ClaimAmount RemainingBalance 1 3 1/1/2010 200 400 2 3 3/3/2010 150 250 SF
From: Uri Dimant on 22 Mar 2010 04:01 SF create table #t (ClaimID int,EmpID int, ClaimDate datetime,ClaimAmount int,RemainingBalance int) insert into #t values (1,3,'20100101',200,null) insert into #t values (2,3,'20100303',150,null) update #t set RemainingBalance= (select 600-sum(ClaimAmount) from #t t where t.ClaimID<=#t.ClaimID and t.EmpID=#t.EmpID) select * from #t "SF" <samnangs(a)pactcambodia.org> wrote in message news:OQhw%23WXyKHA.5776(a)TK2MSFTNGP06.phx.gbl... > Hi, > > I have a table below for tracking the Education Allowance for each > employee. Each employee will receive an annual allowance of USD600. If the > employee made the first request of 200, the remaining balance would be > 400. When the employee request second request of 150 then the balance > would be 600 - (200 + 150) > > My problem is that I cannot find a way to update the remaining balance to > reflect the actual one becauase the RemainingBalance is a recurring > balance. I though that store procedure would be good for the job but I > don't know how to do that? > > Could someone advice? > > > ClaimID EmpID ClaimDate ClaimAmount RemainingBalance > 1 3 1/1/2010 200 > 400 > 2 3 3/3/2010 150 250 > > SF >
From: --CELKO-- on 22 Mar 2010 10:40 CREATE TABLE EducationAccounts (claim_id INTEGER NOT NULL PRIMARY KEY, emp_id INTEGER NOT NULL, REFERENCES Personnel(emp_id), claim_date DATE DEFAULT CURRENT_DATE NOT NULL, claim_amt DECIMAL (8,2) NOT NULL); Why try to keep a running balance? It is usually a bad idea to store computed values. A VIEW with the summary data is probably going to be more useful and certainly shorter. I assume that you want to show the people who have not made a claim also. CREATE VIEW EducationAccountSummary AS SELECT P.emp_id, SUM(E.claim_amt) AS claim_tot, 600.00 SUM(E.claim_amt) AS claim_balance, MAX(E.claim_date) AS most_recent_claim_date FROM Personnel AS P LEFT OUTER JOIN EducationAccounts AS E ON P.emp_id = E.emp_id;
From: Tony Rogerson on 22 Mar 2010 13:44 > CREATE TABLE EducationAccounts > (claim_id INTEGER NOT NULL PRIMARY KEY, > emp_id INTEGER NOT NULL, > REFERENCES Personnel(emp_id), > claim_date DATE DEFAULT CURRENT_DATE NOT NULL, > claim_amt DECIMAL (8,2) NOT NULL); > emp_id? What's an emp_id - don't you mean employee_number? You ought to be using columns based around business definitions. What is _amt? Do you mean claim_amount? Why are you unnecessarily confusing people that follow with obscure abbreviations? > CREATE VIEW EducationAccountSummary > AS > SELECT P.emp_id, SUM(E.claim_amt) AS claim_tot, > 600.00 � SUM(E.claim_amt) AS claim_balance, > MAX(E.claim_date) AS most_recent_claim_date > FROM Personnel AS P > LEFT OUTER JOIN > EducationAccounts AS E > ON P.emp_id = E.emp_id; > That is syntactically incorrect. "--CELKO--" <jcelko212(a)earthlink.net> wrote in message news:519ce683-e139-40c0-86f3-1cd33267616e(a)k5g2000pra.googlegroups.com... > CREATE TABLE EducationAccounts > (claim_id INTEGER NOT NULL PRIMARY KEY, > emp_id INTEGER NOT NULL, > REFERENCES Personnel(emp_id), > claim_date DATE DEFAULT CURRENT_DATE NOT NULL, > claim_amt DECIMAL (8,2) NOT NULL); > > Why try to keep a running balance? It is usually a bad idea to store > computed values. A VIEW with the summary data is probably going to be > more useful and certainly shorter. I assume that you want to show the > people who have not made a claim also. > > CREATE VIEW EducationAccountSummary > AS > SELECT P.emp_id, SUM(E.claim_amt) AS claim_tot, > 600.00 � SUM(E.claim_amt) AS claim_balance, > MAX(E.claim_date) AS most_recent_claim_date > FROM Personnel AS P > LEFT OUTER JOIN > EducationAccounts AS E > ON P.emp_id = E.emp_id; > > >
From: --CELKO-- on 22 Mar 2010 17:35
CREATE VIEW EducationAccountSummary (P.emp_id, claim_tot, claim_balance, most_recent_claim_date) AS SELECT P.emp_id, SUM(E.claim_amt) AS claim_tot, 600.00 SUM(E.claim_amt) AS claim_balance, MAX(E.claim_date) AS most_recent_claim_date FROM Personnel AS P LEFT OUTER JOIN EducationAccounts AS E ON P.emp_id = E.emp_id GROUP BY P.emp_id; |