Stored Procedure Calculation
/*
Created By : Shankar Karuppanna Gounder
Created On : 16.06.2010
Mail Id : shakar_ct@yahoo.co.in
Contact : 09865353972
Description:Newly Created.
*/
USE KCTSHAN
GO
CREATE TABLE dbo.TblEmpmasterDetails
(
EmpID VARCHAR(50),
EmpName VARCHAR(100,
Address VARCHAR(600),
City VARCHAR(200,
DOB DATETIME,
DOJ DATETIME,
PEXP INT,
Gender VARCHAR(20),
Age INT,
Height NUMERIC(5, 2),
BasicSal MONEY,
TA MONEY,
MA MONEY,
PF MONEY,
EPF MONEY,
ESI MONEY,
OA MONEY,
HRA MONEY,
PT MONEY,
AT MONEY,
WEF MONEY,
SPA MONEY,
GTotal MONEY,
NTotal MONEY,
GRADE CHAR(10),
DESIG VARCHAR(100),
BranchCode VARCHAR(50)
)
-----------------------------*************************-------------------------------------------
CREATE TABLE TblGrade
(
Desig VARCHAR(100),
Grade VARCHAR(100)
)
INSERT INTO TblGrade(Desig,Grade)
SELECT 'TRAINEE', 'A1' UNION
SELECT 'J.S.E', 'A2' UNION
SELECT 'S.E', 'B1' UNION
SELECT 'S.S.E', 'B2' UNION
SELECT 'M.L', 'C1' UNION
SELECT 'T.L', 'C1' UNION
SELECT 'P.M', 'D1'
SELECT * FROM TblGrade
-----------------------------------********************--------------------------------------------
CREATE PROCEDURE INS_TblEmpMstDet
(
@EmpID VARCHAR(50) ,
@EmpName VARCHAR(100),
@Address VARCHAR(600),
@City VARCHAR(200),
@DOB DATETIME,
@DOJ DATETIME,
@Gender VARCHAR(20),
@Height numeric(5, 2),
@BasicSal MONEY,
@BranchCode VARCHAR(50),
@Exp INT
)
AS
BEGIN
-----Local Variable Declaration-----
DECLARE @TA MONEY,
@MA MONEY,
@PF MONEY,
@EPF MONEY,
@ESI MONEY,
@OA MONEY,
@HRA MONEY,
@PT MONEY,
@AT MONEY,
@WEF MONEY,
@SPA MONEY,
@GTotal MONEY,
@NTotal MONEY,
@Grade char(10),
@Desig VARCHAR(10),
@Age INT
---------------Calculaton starting here-------------------
--Salary Calculation
SET @TA = (@BasicSal/100)*5
SET @MA = (@BasicSal/100)*2
SET @PF = (@BasicSal/100)*11.5
SET @EPF = (@BasicSal/100)*3
SET @ESI = (@BasicSal/100)*2
SET @OA = (@BasicSal/100)*12
SET @HRA = (@BasicSal/100)*12
SET @PT = (@BasicSal/100)*7
SET @AT = (@BasicSal/100)*6
SET @WEF = (@BasicSal/100)*1
SET @SPA = (@BasicSal/100)*6
SET @GTotal = @BasicSal+@TA+@MA+@PF+@EPF+@ESI+@OA+@HRA+@PT+@AT+@WEF+@SPA
SET @NTotal = @GTotal - (@PF+@EPF+@ESI+@PT+@AT+@WEF)
--Age Calculation
SELECT @Age = DATEDIFF(yyyy,@DOB,GETDATE())
--Designation Calculation
IF @Exp = 0
BEGIN
SET @Desig = 'TRAINEE'
END
ELSE IF @Exp <= 1
BEGIN
SET @Desig = 'J.S.E'
END
ELSE IF @Exp > 1 AND @Exp <= 2
BEGIN
SET @Desig = 'S.E'
END
ELSE IF @Exp > 2 AND @Exp <= 3
BEGIN
SET @Desig = 'S.S.E'
END
ELSE IF @Exp > 3 AND @Exp <= 4
BEGIN
SET @Desig = 'M.L'
END
ELSE IF @Exp > 4 AND @Exp <= 6
BEGIN
SET @Desig = 'T.L'
END
ELSE IF @Exp > 6 AND @Exp <= 10
BEGIN
SET @Desig = 'P.M'
END
--Grade Calculation
SET @Grade =CASE @Desig
WHEN 'TRAINEE' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'J.S.E' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'S.E' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'S.S.E' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'M.L' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'T.L' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
WHEN 'P.M' THEN (SELECT Grade FROM TblGrade WHERE Desig=@Desig)
ELSE 'None'
END
-------------------Calculaton Ended here----------------------
IF NOT EXISTS ( SELECT * FROM dbo.TblEmpmasterDetails(nolock) WHERE EmpID = @EmpID)
BEGIN
INSERT INTO TblEmpmasterDetails
(
EmpID,EmpName,Address,City,DOB,DOJ,Gender,Age,Height,
BasicSal,TA,MA,PF,EPF,ESI,OA,HRA,
PT,AT,WEF,SPA,GTotal,NTotal,Grade,Desig,BranchCode
)
VALUES
(
@EmpID,@EmpName,@Address,@City,@DOB,@DOJ,@Gender,@Age,@Height,
@BasicSal,@TA,@MA,@PF,@EPF,@ESI,@OA,@HRA,
@PT,@AT,@WEF,@SPA,@GTotal,@NTotal,@Grade,@Desig,@BranchCode
)
SELECT @EmpID + ' Employee Registered Successfully...'
SELECT *
FROM TblEmpmasterDetails
END
ELSE
BEGIN
SELECT 'Employee Alredy Exists ...'
SELECT * FROM TblEmpmasterDetails(nolock) WHERE EmpId=@EmpId
END
END
GO
--EXEC INS_TblEmpMstDet 'EMP107','Shankar Karuppanna','Namakkal','Salem','09/10/1983','06/16/2010','Male',5.1,8000.00,'009',4