USE [HRRecruitment]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GenerateReport]
(
@StartDate DATE,
@EndDate DATE,
@Email nvarchar(40)
)
as
begin
Declare @start VarChar, @end VarChar
If not exists(select * from tblWorkExpDetails where Email=@Email)
begin
select
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else if not exists(select * from tblFresherDetails where Email=@Email)
begin
select
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
else
begin
select
pd.FirstName,
pd.LastName,
pd.Mobile,
pd.Email,
pd.Sources,
ed.XthPercentage,
ed.XIIthPercentage,
ed.GPercentage,
ed.PGPercentage,
wed.CurrentSalary,
wed.TotalExperience,
fd.Typing
from
tblPersonalDetails pd
Inner Join tblEducationalDetails ed on pd.Email=ed.Email
Inner Join tblWorkExpDetails wed on pd.Email=wed.Email
Inner Join tblFresherDetails fd on pd.Email=fd.Email
where pd.JoinDate Between @StartDate and @EndDate
end
end;