Optimize Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Optimize Query

Author

Topic

Rashednadeem
Starting Member

5 Posts

Posted - 2014-07-14 : 14:09:19

Please Help to optimize this SP

ALTER PROCEDURE [dbo].[Att_AttendanceReport]
-- Variables
@EMPLOYEEID INT =NULL,
@DEPARTMENTID INT =NULL,
@SECTIONID INT =NULL,
@FROMDATE datetime =NULL,
@TODATE datetime=NULL,
@Status varchar(30)=Null
AS
declare @holiday varchar(30)=NULL
-- Create Temp Table to hold Recods
--Declare @HellTable TABLE (RecordID int IDENTITY (1,1) PRIMARY KEY,EmployeeID INT,[Record Date] DATETIME,EmployeeNumber INT,EmployeeName VARCHAR(30)
-- ,DepartmentId INT,DepartmentName VARCHAR(30),Photo VARCHAR(30),SectionID INT,Designation VARCHAR(30),Arrival DATETIME, Departure DATETIME,
-- StatusName VARCHAR(30),HoursWorked time(7))
-- Assign Date Range
IF @FROMDATE is Null
SET @FROMDATE=CONVERT(VARCHAR(11),GETDATE())
Else
SET @FROMDATE=CONVERT(VARCHAR(11),@FROMDATE)

IF @TODATE is NULL
SET @TODATE=CONVERT(VARCHAR(11),GETDATE())
else
SET @TODATE=CONVERT(VARCHAR(11),@ToDate)
-- Loop Though Date range
WHILE @FROMDATE <= @TODATE
BEGIN
-- insert to Temp Table
-- Check Holiday
Set @holiday=dbo.fn_GETHOLIDAYS(@FromDate)
if @holiday is Null
SET @Holiday='Absent'
Declare @hellTable TABLE (EmployeeID int, RecordDate datetime,employeeNumber int,EmployeeName varchar(30),
DepartmentID int, DepartmentName varchar(30), Photo varchar(30), SectionID int,Designation varchar(50) ,Arrival DateTime,Departure DateTime,
StatusName varchar(30),hoursWorked time(7))

INSERT @hellTable(EmployeeID,RecordDate ,EmployeeNumber ,EmployeeName
,DepartmentID,DepartmentName,Photo,SectionID,Designation ,Arrival , Departure,
StatusName ,HoursWorked )

SELECT e.employeeid,
ISNULL(ar.RecordDate, @FROMDATE) AS 'Record Date',
E.EmployeeNumber,
e.EmployeeName,
e.DepartmentID,
d.DepartmentName,
e.Photo,
ISNULL(e.SectionID,0) AS SectionID,
ISNULL(ds.JobTitle,'Unknown')as 'Designation',
ar.Arrival,
ar.Departure,
ISNULL(s.StatusName,@Holiday) AS StatusName,
ISNULL(ar.hoursworked,'00:00') AS 'HoursWorked'
FROM Employees e
LEFT OUTER JOIN EmployeeAttendanceRecords ar
ON e.EmployeeID= ar.employeeid
AND RecordDate = @FROMDATE
OR RecordDate IS NULL
LEFT JOIN AttendanceStatus s
ON ISNULL(ar.AttendanceStatusID, 0) = s.StatusID
JOIN Departments d on d.DepartmentID=e.DepartmentID
JOIN Designations ds on ds.DesignationID=e.designationID
WHERE e.AttendanceEnabled=1
SET @FROMDATE=DATEADD(day,1,@FROMDATE)
END
--Select Record
SELECT EmployeeID,Recorddate As RecordDate ,EmployeeNumber ,EmployeeName
,DepartmentID,DepartmentName,Photo,SectionID,Designation ,Arrival , Departure,
StatusName ,CONVERT(VARCHAR(5),Hoursworked, 108) as HoursWorked FROM @HellTable te
Where
(te.EmployeeID=COALESCE(@EmployeeID,te.EmployeeID)
AND te.DepartmentID=COALESCE(@DepartmentID,te.DepartmentID)
AND te.SectionID =COALESCE(@SectionID,te.SectionID)
AND te.StatusName LIKE COALESCE(@Status + '%',te.StatusName))
ORDER BY RecordDate desc,DepartmentName,StatusName Desc

Rashed

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-14 : 14:18:26

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources