Query Result Slows Down If i Use Case Statement.

[nasirpinnacle]

I wrote a Function who returns around 800000 records in 17-20 Seconds but if i use 7 differernt simple case Statements its time increases to 03 min and 10 seconds.. Can somebody help me.

Table Structure:
Create Table Attendence (dated DateTime,
Location_ID VarChar(3),
fs_time VarChar(6),
employee_id VarChar(32),
Present SmallInt,
time_in VarChar(8),
TimeIN_AMPM VarChar(2),
time_out VarChar(8),
TimeOut_AMPM VarChar(2),
LunchTime_Out VarChar(8),
LunchTime_In VarChar(8),
over_time VarChar(8),
FullHalf TinyInt,
TypeOfLeave VarChar(12),
LCReason VarChar(100),
BTEarlyOutgoingReason VarChar(100),
BTLCReason VarChar(100),
EarlyOutgoingReason VarChar(100),
ManuallyTemperred TinyInt,
DontCalculateOTFromTime TinyInt,

IsGazetted TinyInt,
HToM_TimeIn SmallInt,
HToM_TimeOut SmallInt,
HToM_OverTime SmallInt,
ManualOverTime Varchar(8),
HToM_ManualOverTime SmallInt,
LeaveForm_TypeOfLeave Varchar(12),
LeaveForm_FullHalf TinyInt,
CPLGivenOn DateTime,
MarkedSandwich TinyInt,
CurrentDeptt Varchar(12),
CurrentDesig Varchar(3),
CurrentShift Varchar(3),
ShortLeave_WOP TinyInt Default 0,
ShortLeave_Leave TinyInt Default 0,
ShortLeave_TypeOfLeave Varchar(12),
Constraint pk_Attendence Primary Key (Dated, fs_time, Employee_id),
Constraint fk_Attendence_EmployeeDetails Foreign Key (Employee_id) References EmployeeDetails (id),
Constraint fk_Attendence_CDeptt Foreign Key (CurrentDeptt) References DefDepartments (ID),
Constraint fk_Attendence_CDesig Foreign Key (CurrentDesig) References DefDesignations (ID),
Constraint fk_Attendence_CShift Foreign Key (CurrentShift) References DefShifts (ID)
);
=========Function===================
Alter Function f_LeaveRecords (@EmpID Varchar(32), @FromDate VarChar(11), @ToDate VarChar(11),
@iDeptt Varchar(12), @iDesig Varchar(12))
Returns Table
as
Return (
Select T.Dated,

T.Time_In TimeIn,

T.Time_Out TimeOut,

IsNull(T.ManuallyTemperred,0) ManuallyTemperred,
ED.ID Employee_ID,
Case When ED.Daily=1 then 0 When ED.Monthly=1 Then 1 When ED.Contract=1 Then 2
When ED.PieceRate=1 Then 3 End TypeOfEmployee,

Case When IsNull(T.IsGazetted,0)<>0 and IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'')='' Then 'GZ'
When IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'')='' and Present=0 and (IsNull(IsNull(T.LeaveForm_FullHalf,T.FullHalf),0) <>0 or ((IsNull(Time_In,' : ')=' : ' or IsNull(Time_In,'')=''))) Then 'Absent'
Else IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'') End TypeOfLeave,

Case When ((DatePart(dw, T.Dated)=PD1.DayNo or DatePart(dw, T.Dated)=PD2.DayNo or IsNull(T.IsGazetted,0)=1) and T.Present=0 and IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'')='') Then 1
When IsNull(T.Present, 0) <>0 Then
Case When IsNull(IsNull(T.LeaveForm_FullHalf,T.FullHalf),0)= 0 Then 1 Else 0.5 End
When T.Time_IN<>' : ' and T.Time_IN<>'' and (T.Time_Out='' or T.Time_Out=' : ') Then 0
Else 0 End DayPresent,

Case When IsNull(IsNull(T.LeaveForm_FullHalf,T.FullHalf),0)= 0 Then 0 Else 1 End HalfLeave,

Case When IsNull(IsNull(T.LeaveForm_FullHalf,T.FullHalf),0)< >0 Then 0.5
When IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'') In ('Absent', 'LWP', 'L.W.Pay', 'L.W/O.Pay') and T.Present=0 and ((IsNull(T.Time_In, ' : ')=' : ' or T.Time_In='') and (IsNull(T.Time_Out, ' : ')=' : ' or T.Time_Out='')) Then 1
When IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'')<>'' and IsNull(IsNull(T.LeaveForm_TypeOfLeave,T.TypeOfLeav e),'')<>'Rest' and IsNull(T.IsGazetted,0)=0 Then 1 Else 0 End+
((IsNull(T.ShortLeave_WOP,0)+IsNull(T.ShortLeave_L eave,0))*IsNull(GS.ShortLeaveEqualToDeductionOfLea ve,0)) LeavesCounted,

T.CurrentDeptt Deptt_ID, T.CurrentDesig Desig_ID, T.CurrentShift Shift_ID
From Attendence T
Left Join EmployeeDetails ED on T.Employee_ID=ED.ID
-- Left Join period on period.From_DT=Period.From_DT
Left Join PrintDays PD1 on ED.RestDay=PD1.ID
Left Join PrintDays PD2 on ED.RestDayOther=PD2.ID
-- Left Join DefDepartments DD on DD.ID=T.CurrentDeptt
-- Left Join DefDesignations DG on DG.ID=T.CurrentDesig
-- Left Join DefShifts DS on DS.ID=T.CurrentShift
Left Join GlobalSettings GS On 1=1
Where (@EmpID='' or T.Employee_ID=@EmpID)
and (@FromDate='' or T.Dated Between @FromDate and @ToDate)
)
================Simple Query Which Takes 19 Seconds=====
Select * from f_LeaveRecords ('', '01-Jul-2001', '30-Jun-2010', '', '') T
================Other Query Which Takes More than 3 Minutes=======
Select *,
Case When T.TypeOfLeave='Casual' Then T.LeavesCounted Else 0 End Casual,
Case When T.TypeOfLeave='Annual' Then T.LeavesCounted Else 0 End Annual,
Case When T.TypeOfLeave='Sick' Then T.LeavesCounted Else 0 End Sick,
Case When T.TypeOfLeave In ('LWP', 'L.W.Pay', 'L.W/O.Pay') Then T.LeavesCounted Else 0 End LWP,
Case When T.TypeOfLeave='Absent' Then T.LeavesCounted Else 0 End Absent,
Case When T.TypeOfLeave='GZ' Then T.LeavesCounted Else 0 End GZ,
Case When T.TypeOfLeave='Rest' Then T.LeavesCounted Else 0 End Rest from f_LeaveRecords ('', '01-Jul-2001', '30-Jun-2010', '', '') T