-- 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.
(
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
Vikrant MorePosted Feb 15, 2015, 8:15 AM
DECLARE @InterestRate INT,@CPFNO_Min INT,@CPFNO_Max INT,@LOANMTH INT
SET @InterestRate=10
SELECT @CPFNO_Min=MIN(CPFNO),@CPFNO_Max=MAX(CPFNO) FROM dbo.tblmyStoreTable
WHILE(@CPFNO_Min <= @CPFNO_Max)
BEGIN
SELECT @LOANMTH = LOANMTH FROM dbo.tblmyStoreTable WHERE CPFNO = @CPFNO_Min
IF(@LOANMTH>0)
BEGIN
INSERT INTO tblFinalTable
SELECT CPFNO,LOANMTH,EMP_OP,L_GRANT,(((EMP_OP-L_GRANT)*@InterestRate/100)/12)*((12-(LOANMTH))+1) AS EmpInterest FROM tblmyStoreTable WHERE CPFNO = @CPFNO_Min
END
ELSE IF(@LOANMTH = 0)
BEGIN
INSERT INTO tblFinalTable
SELECT CPFNO,LOANMTH,EMP_OP,L_GRANT,(EMP_OP*@InterestRate/100) AS EmpInterest FROM tblmyStoreTable WHERE CPFNO = @CPFNO_Min
END
SELECT @CPFNO_Min=MIN(CPFNO) FROM dbo.tblmyStoreTable
WHERE CPFNO > @CPFNO_Min
END
Jitendra KumarPosted Jan 19, 2015, 11:57 AM
What problem you facing??