how to insert data from one table to another after calculating interest

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

-- I have a table "myStoreTable".which contain 4 columns.
-- I a have another table "tblFinalTable", which contain 5 columns.
-- I want to insert data into "tblFinalTable" from "tblmyStoreTable".
-- But, not directly inserted,after calculating the "EmpInterest", it inserted into tblFinalTable table.
-- But i am facing problem while try to insert all the rows in tblFinalTable after calculating interest based on "LOANMTH" condition.
-- I am a fresher.please help.Thanks in advance.

SQL

CREATE TABLE dbo.tblmyStoreTable
(
	CPFNO int NULL,
	LOANMTH int NULL,
	EMP_OP int NULL,
	L_GRANT int NULL
) 

INSERT INTO tblmyStoreTable(100,0,20000,0)
INSERT INTO tblmyStoreTable(200,2,40000,15000)
INSERT INTO tblmyStoreTable(300,0,50000,0)
INSERT INTO tblmyStoreTable(400,5,70000,20000)
INSERT INTO tblmyStoreTable(500,0,100000,0)

CREATE TABLE dbo.tblFinalTable
(
	CPFNO int NULL,
	LOANMTH int NULL,
	EMP_OP int NULL,
	L_GRANT int NULL,
	EmpInterest INT
) 

declare @InterestRate INT
SET @InterestRate=10


 IF(@LOANMTH>0)      
    BEGIN
         INSERT INTO tblFinalTable
         SELECT CPFNO,LOANMTH,(((EMP_OP-L_GRANT)*@InterestRate/100)/12)*((12-(LOANMTH))+1) AS EmpInterest FROM tblmyStoreTable 
		
    END
 ELSE
    BEGIN
         INSERT INTO tblFinalTable
		 SELECT CPFNO,LOANMTH,(EMP_OP*@InterestRate/100) as EmpInterest FROM tblmyStoreTable 
    END

Posted 19-Jan-15 2:18am

bubai banerjee

Updated 19-Jan-15 2:20am

Deepu S Nair

v2

Add a Solution

Comments

CHill60 19-Jan-15 8:20am

What problem?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jaket-cp · Answer 1 · 2015-01-19T02:48:00

A select case query can be used to get the desired result if I get the criteria correctly.

SQL

--query like so or similar can be used for the insert into tblFinalTable
SELECT 
	*,
	Case 
		When LOANMTH > 0 Then
			(((EMP_OP-L_GRANT)*@InterestRate/100)/12)*((12-(LOANMTH))+1)
		Else
			(EMP_OP*@InterestRate/100)
		End as EmpInterest
FROM tblmyStoreTable 
;
--note: make sure the number of columns in the insert and the select match

Not sure the purpose of tblFinalTable table as the results can be derived from the tblmyStoreTable table as can been seen in the select query above.

Read up about CASE (Transact-SQL): http://msdn.microsoft.com/en-GB/library/ms181765(v=sql.105).aspx[^]

Richard Deeming · Answer 2 · 2015-01-19T02:52:00

The problem is that tblFinalTable contains five columns, but your SELECT statement only returns three columns.

Try:

SQL

INSERT INTO tblFinalTable
SELECT
    CPFNO,
    LOANMTH,
    EMP_OP,
    L_GRANT,
    CASE
        WHEN @LoanMth > 0 THEN (((EMP_OP - L_GRANT) * @InterestRate / 100) / 12) * ((12 - LOANMTH) + 1)
        ELSE (EMP_OP * @InterestRate / 100)
    END
FROM
    tblMyStoreTable
;

how to insert data from one table to another after calculating interest

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0