query timeout expired

See more:

SQL-Server

Please any one help me. I have SQL Function. Inside this function i use some other function and also use some CURSOR to FETCH record and insert this value to the temporary table.

Finally when i execute this Function in the VB6.0 Application at the first when My Database Server is on at every morning, it display this type of Message. When i execute this Function Inside the SQL Server Query analyzer and after that when again execute this Function from my VB6.0 Application then it works properly.

I can not understand what is the problem.

Bipul

-----------------------------------------------------------------------------------------------
Main Function
-----------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[FN_Attendance_RPT]
(
		@StartDate varchar(11),
		@EndDate varchar(11),
		@EmployeeID varchar(1000)
)
RETURNS @Attend TABLE(
		Att_Id int PRIMARY KEY IDENTITY,
		AttendanceDate smalldatetime null,
		EmployeeName varchar(50) null,
		EmployeeGroup varchar(2) null,
		InTime time(7) null,
		Late varchar(50) null,
		OutTime time(7) null,
		TotalWork varchar(50) null,
		AtOffice varchar(50) null,
		OfficeStartHour varchar(15) null,
		WorkingHour int null,
		OutOfficeVisit float null,
		Comments varchar(5000) null,
		Purpose varchar(1000) null,
		TotalWorkingDay int null,
		IndividualWorkingDay int null,
		OverTime varchar(50) null,
		OutOffice varchar(50) null
)
AS
BEGIN
	--DECLARE @EmployeeID		VARCHAR(1000)
	DECLARE @Part			VARCHAR(1000)
	DECLARE @IND			INT
	DECLARE @EIND			INT 
	DECLARE @EmpID			INT
	DECLARE @CUR_ATT		CURSOR
	DECLARE @CUR_IWDAY		CURSOR
	DECLARE @CUR_TWDay		CURSOR
	
	DECLARE	@AttendanceDate As smalldatetime,
			@EmployeeName As varchar(50),
			@EmployeeGroup AS varchar(2),
			@InTime As time(7),
			@Late As varchar(50),
			@OutTime As time(7),
			@TotalWork As varchar(50),
			@AtOffice As varchar(50),
			@OfficeStartHour As varchar(15),
			@OutOfficeVisit As float,
			@WorkingHour As int,
			@Comments As varchar(5000),
			@Purpose As varchar(1000),
			@TotalWorkingDay As int,
			@IndividualWorkingDay As int,
			@Holiday As varchar(1000),
			@AttDate As smalldatetime,
			@AttDate1 As smalldatetime,
			@TotalLeave As int,
			@OverTime As varchar(50),
			@OutOffice As varchar(50),
			@WorkingMonth As smalldatetime
			
	--SET @EmployeeID = ',1,21,30,25,50,65,80,70,54,45,69,'
	SET @IND = CHARINDEX(',',@EmployeeID)
	SET @EIND = 0
	WHILE(@IND != LEN(@EmployeeID))
		BEGIN
			SET  @EIND = ISNULL(((CHARINDEX(',', @EmployeeID, @IND + 1)) - @IND - 1), 0)
			SET @EmpID = CAST((SELECT (SUBSTRING(@EmployeeID, (@IND  + 1),  @EIND))) As INT)
			SELECT @IND = ISNULL(CHARINDEX(',', @EmployeeID, @IND + 1), 0)
			
			IF CURSOR_STATUS('local','@CUR_ATT')=1
				BEGIN
					CLOSE  @CUR_ATT
					DEALLOCATE @CUR_ATT
				END

			SET @CUR_ATT = CURSOR STATIC FOR
			SELECT AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, OverTime, OutOffice, TotalWorkingDay, IndividualWorkingDay FROM FN_Attendance(@StartDate, @EndDate, @EmpID) ORDER BY AttendanceDate
			OPEN @CUR_ATT
			
			FETCH @CUR_ATT INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @Late, @OutTime, @TotalWork, @AtOffice, @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @OverTime, @OutOffice, @TotalWorkingDay, @IndividualWorkingDay
			WHILE @@FETCH_STATUS = 0
				BEGIN
					INSERT INTO @Attend
					(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, OverTime, OutOffice, TotalWorkingDay, IndividualWorkingDay)
					VALUES (@AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @Late, @OutTime, @TotalWork, @AtOffice, @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @OverTime, @OutOffice, @TotalWorkingDay, @IndividualWorkingDay)
					FETCH @CUR_ATT INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @Late, @OutTime, @TotalWork, @AtOffice, @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @OverTime, @OutOffice, @TotalWorkingDay, @IndividualWorkingDay
				END
			
-- Total Individual Working Day Count By Month
			IF CURSOR_STATUS('local','@CUR_IWDAY')=1
				BEGIN
					CLOSE  @CUR_IWDAY
					DEALLOCATE @CUR_IWDAY
				END

			SET @CUR_IWDAY = CURSOR STATIC FOR
			--SELECT (SELECT Name FROM Personal WHERE pId = @EmpID) As EmpName, COUNT(DISTINCT I.EDate), CAST((CAST(MONTH(I.EDate) As varchar(2)) + '/1/' + CAST(YEAR(I.EDate) As varchar(4))) As DATE) As WorkingMonth
			--FROM Information AS I LEFT JOIN OffCalender AS O ON I.EDate=O.CalDate 
			--WHERE I.id = @EmpID And I.EDate BETWEEN @StartDate And @EndDate And (O.ImplementedON <> 'ALL' Or O.CalDate IS NULL) 
			--GROUP BY MONTH(I.EDate), YEAR(I.EDate)
			SELECT (SELECT Name FROM Personal WHERE pId = @EmpID) As EmpName, COUNT(DISTINCT A.WorkingDay) As IndividualWorkingDay, CAST((CAST(MONTH(A.WorkingDay) As varchar(2)) + '/1/' + CAST(YEAR(A.WorkingDay) As varchar(4))) As DATE) As WorkingMonth
			FROM TotalWorkingDay AS A LEFT JOIN 
			(SELECT DISTINCT LD.LeaveDate AS LeaveDate 
			FROM LeaveDetails AS LD, LeaveReason AS LR 
			WHERE LD.LeaveID = LR.LeaveID And LR.eId=@EmpID AND LD.LeaveDate BETWEEN @StartDate And @EndDate 
			UNION ALL
			SELECT CalDate AS LeaveDate FROM OffCalender 
			WHERE CalDate BETWEEN @StartDate And @EndDate 
			AND ImplementedOn = (SELECT empGroup FROM Personal WHERE pId = @EmpID)
			/*
			UNION ALL
			SELECT C.AttDate AS AttDate 
			FROM Comments As C INNER JOIN Personal As P ON C.CID = P.PID 
			WHERE C.CID = @EmpID AND C.AttDate BETWEEN @StartDate And @EndDate And
			C.AttDate NOT IN(SELECT distinct edate FROM Information WHERE id = @EmpID And edate BETWEEN @StartDate And @EndDate)*/) AS B ON A.WorkingDay=B.LeaveDate 
			WHERE B.LeaveDate Is Null AND A.WorkingDay BETWEEN @StartDate And @EndDate
			GROUP BY MONTH(A.WorkingDay), YEAR(A.WorkingDay)
			OPEN @CUR_IWDAY
			
			FETCH @CUR_IWDAY INTO @EmployeeName, @IndividualWorkingDay, @WorkingMonth
			WHILE @@FETCH_STATUS = 0
				BEGIN
					UPDATE @Attend 
					SET IndividualWorkingDay = @IndividualWorkingDay
					WHERE MONTH(AttendanceDate) = MONTH(@WorkingMonth) And EmployeeName = @EmployeeName
					FETCH @CUR_IWDAY INTO @EmployeeName, @IndividualWorkingDay, @WorkingMonth
				END
		END
-- Total Working Day Count By Month
	SET @IND = CHARINDEX(',',@EmployeeID)
	SET @EIND = 0
	WHILE(@IND != LEN(@EmployeeID))
		BEGIN
			SET  @EIND = ISNULL(((CHARINDEX(',', @EmployeeID, @IND + 1)) - @IND - 1), 0)
			SET @EmpID = CAST((SELECT (SUBSTRING(@EmployeeID, (@IND  + 1),  @EIND))) As INT)
			SELECT @IND = ISNULL(CHARINDEX(',', @EmployeeID, @IND + 1), 0)
			
			IF CURSOR_STATUS('local','@CUR_TWDay')=1
			BEGIN
				CLOSE  @CUR_TWDay
				DEALLOCATE @CUR_TWDay
			END

			SET @CUR_TWDay = CURSOR STATIC FOR
			SELECT SUM(TotalWorkingDay), WorkingMonth FROM FN_WorkingDayCount (@StartDate, @EndDate, @EmpID)
			GROUP BY WorkingMonth
			ORDER BY WorkingMonth
			OPEN @CUR_TWDay

			FETCH @CUR_TWDay INTO @TotalWorkingDay, @WorkingMonth
			WHILE @@FETCH_STATUS = 0
			BEGIN
				UPDATE @Attend 
				SET TotalWorkingDay = @TotalWorkingDay
				WHERE MONTH(AttendanceDate) = MONTH(@WorkingMonth) And Year(AttendanceDate) = Year(@WorkingMonth) And Comments = 'Absent'
				FETCH @CUR_TWDay INTO @TotalWorkingDay, @WorkingMonth
			END
		END
	RETURN
END
-----------------------------------------------------------------------------------------------
2nd Function
-----------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[FN_Attendance]
(
		@StartDate varchar(11),
		@EndDate varchar(11),
		@EmployeeID int
)
RETURNS @Attendance TABLE(
		Att_Id int PRIMARY KEY IDENTITY,
		AttendanceDate smalldatetime null,
		EmployeeName varchar(50) null,
		EmployeeGroup varchar(2) null,
		InTime varchar(50) null,
		Late varchar(50) null,
		OutTime varchar(50) null,
		TotalWork varchar(50) null,
		AtOffice VARCHAR(50) null,
		OfficeStartHour varchar(15) null,
		WorkingHour int null,
		OutOfficeVisit float null,
		Comments varchar(5000) null,
		Purpose varchar(1000) null,
		TotalWorkingDay int null,
		IndividualWorkingDay int null,
		OutOffice varchar(50) null,
		OverTime varchar(50) null
)
AS
BEGIN
	DECLARE	@AttendanceDate As smalldatetime,
			@EmployeeName As varchar(50),
			@EmployeeGroup AS varchar(2),
			@InTime As time(7),
			@Late As varchar(5),
			@OutTime As time(7),
			@TotalWork As varchar(5),
			@AtOffice As VARCHAR(5),
			@OfficeStartHour As varchar(15),
			@OutOfficeVisit As float,
			@WorkingHour As int,
			@Comments As varchar(5000),
			@Purpose As varchar(1000),
			@TotalWorkingDay As int,
			@IndividualWorkingDay As int,
			@Holiday As varchar(1000),
			@Holiday1 As varchar(1000),
			@Holiday2 As varchar(1000),
			@AttDate As smalldatetime,
			@AttDate1 As smalldatetime,
			@AttDateDBL As smalldatetime,
			@TotalLeave As int,
			@TotalAbsent As int,
			@WorkingMonth As Date
	DECLARE @CUR_ATT CURSOR
	DECLARE @CUR_ATTALTHDAY CURSOR
	DECLARE @CUR_LEAVE CURSOR
	DECLARE @CUR_ABSENT CURSOR
	DECLARE @CUR_CALSTAY CURSOR
	DECLARE @CUR_TWDay CURSOR
	DECLARE @CUR_ABSDAY CURSOR
-- Other Type of Attendance

	IF CURSOR_STATUS('local','@CUR_ABSDAY')=1
		BEGIN
			CLOSE  @CUR_ABSDAY
			DEALLOCATE @CUR_ABSDAY
		END

	SET @CUR_ABSDAY = CURSOR STATIC FOR
	SELECT C.AttDate AS AttDate, P.Name AS EmployeeName, P.EmpGroup, C.Comments AS Comments
	FROM Comments As C, Personal As P
	WHERE C.CID = P.PID  And C.CID = @EmployeeID AND C.AttDate BETWEEN @StartDate And @EndDate And
	C.AttDate NOT IN(SELECT distinct edate FROM Information WHERE id = @EmployeeID And edate BETWEEN @StartDate And @EndDate)
	--WHERE C.CID = P.PID  And C.CID = @EmployeeID And C.AttDate BETWEEN @StartDate And @EndDate And
	--C.AttDate NOT IN(SELECT AttendanceDate FROM @Attendance)
	ORDER BY C.AttDate
	OPEN @CUR_ABSDAY
	
	FETCH @CUR_ABSDAY INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @Comments
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @Attendance
				(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, TotalWorkingDay)
				VALUES (@AttendanceDate, @EmployeeName, @EmployeeGroup, '', '--', '', 0.00, 0.00 , @OfficeStartHour, @WorkingHour, '0.00', @Comments, '', 0)
			FETCH @CUR_ABSDAY INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @Comments
		END
-----------------------------------------------------------------------------------------------
	IF CURSOR_STATUS('local','@CUR_ATT')=1
		BEGIN
			CLOSE  @CUR_ATT
			DEALLOCATE @CUR_ATT
		END

	SET @CUR_ATT = CURSOR STATIC FOR
	SELECT EDate As AttendanceDate, 
	(SELECT Name FROM Personal WHERE pId = @EmployeeID) As EmployeeName, 
	(SELECT empGroup FROM Personal WHERE pId = @EmployeeID) As EmployeeGroup, 
	Min(CAST(LEFT(ETime, 8) As time(7))) As InTime, 
	Max(CAST(LEFT(ETime, 8) As time(7))) As OutTime, 
	'' As TotalWork, '' As TotalStayTime,
	(SELECT TOP 1 PD.StartHour FROM PersonalAgreement PD, Information AS i WHERE PD.EID = i.ID AND i.id = @EmployeeID AND PD.SDate <= EDate ORDER BY PD.SDate DESC) As OfficeStartHour, 
	(SELECT TOP 1 PD.Workhour FROM PersonalAgreement PD, Information AS i WHERE PD.EID=i.ID AND PD.SDate <= EDate ORDER BY PD.SDate DESC) As WorkingHour,
	(SELECT Duration FROM Comments C WHERE CID = @EmployeeID AND C.attDate = EDate) As OutOfficeVisit,
	(SELECT Comments FROM Comments C WHERE CID = @EmployeeID AND C.attDate = EDate) As Comments,
	(SELECT Purpose FROM Comments C WHERE CID = @EmployeeID AND C.attDate = EDate) As Purpose,
	0 As TotalWorkingDay,
	(SELECT COUNT(OC.CalDate) FROM OffCalender As OC, Holiday As H WHERE H.Id = OC.holidayId And OC.ImplementedOn = (SELECT empGroup FROM Personal WHERE PID = @EmployeeID) And CalDate BETWEEN @StartDate and @EndDate) As IndividualWorkingDay
	FROM Information 
	WHERE ID = @EmployeeID AND EDate BETWEEN @StartDate and @EndDate 
	GROUP BY EDate 
	ORDER BY EDate
	
	SET @TotalLeave = 0
	SET @TotalLeave = (SELECT COUNT(LD.LeaveDate) FROM LeaveReason AS LR, LeaveDetails AS LD WHERE LR.LeaveID = LD.LeaveID And LR.EID = @EmployeeID AND LD.LeaveDate BETWEEN @StartDate and @EndDate)

	OPEN @CUR_ATT

	IF CURSOR_STATUS('local','@CUR_ATTALTHDAY')=1
	BEGIN
		CLOSE  @CUR_ATTALTHDAY
		DEALLOCATE @CUR_ATTALTHDAY
	END

	SET @CUR_ATTALTHDAY = CURSOR STATIC FOR
	SELECT OC.CalDate As AttDate, H.holidayName As Holiday
	FROM OffCalender As OC, Holiday As H
	WHERE H.Id = OC.holidayId And (OC.ImplementedOn = (SELECT empGroup FROM Personal WHERE PID = @EmployeeID) OR OC.ImplementedOn = 'All') And CalDate BETWEEN @StartDate and @EndDate
	ORDER BY OC.CalDate
	OPEN @CUR_ATTALTHDAY
	
	IF CURSOR_STATUS('local','@CUR_LEAVE')=1
	BEGIN
		CLOSE  @CUR_LEAVE
		DEALLOCATE @CUR_LEAVE
	END

	SET @CUR_LEAVE = CURSOR STATIC FOR
	SELECT LD.LeaveDate, LR.Comments AS Comments FROM LeaveReason AS LR, LeaveDetails AS LD 
	WHERE LR.LeaveID = LD.LeaveID And LR.EID = @EmployeeID AND LD.LeaveDate BETWEEN @StartDate and @EndDate
	ORDER BY LD.LeaveDate
	OPEN @CUR_LEAVE

	FETCH @CUR_ATT INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @OutTime, @TotalWork, @AtOffice, @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @TotalWorkingDay, @IndividualWorkingDay
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @Attendance
			(AttendanceDate, EmployeeName, EmployeeGroup, InTime, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, TotalWorkingDay, IndividualWorkingDay)
			VALUES (@AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @OutTime, '0.00', (CASE ISNUMERIC(@AtOffice) WHEN 1 THEN CAST(@AtOffice AS float) ELSE 0.00 END), @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @TotalWorkingDay, (@IndividualWorkingDay+@TotalLeave))
			FETCH @CUR_ATT INTO @AttendanceDate, @EmployeeName, @EmployeeGroup, @InTime, @OutTime, @TotalWork, @AtOffice, @OfficeStartHour, @WorkingHour, @OutOfficeVisit, @Comments, @Purpose, @TotalWorkingDay, @IndividualWorkingDay
		END

	IF CURSOR_STATUS('local','@CUR_CALSTAY')=1
		BEGIN
			CLOSE  @CUR_CALSTAY
			DEALLOCATE @CUR_CALSTAY
		END

	SET @CUR_CALSTAY = CURSOR STATIC FOR
	SELECT AttnDate, (CAST((SUM(CAST(TotalStayTime As int)))/60 as varchar) + '.' + REPLICATE('0', 2 - Len(((SUM(CAST(TotalStayTime As int)))%60)*100/60)) + cast (((SUM(CAST(TotalStayTime As int)))%60)*100/60 as varchar)) As AtOffice
	--SELECT AttnDate, CAST(CONVERT(decimal(5,2),(SUM(CONVERT(decimal(5,2),TotalStayTime))/CONVERT(decimal(5,2),60))) As varchar(5)) As AtOffice
	FROM FN_TotalStayTime (@StartDate, @EndDate, @EmployeeID) 
	WHERE AttnDate BETWEEN @StartDate And @EndDate
	GROUP BY AttnDate
	ORDER BY AttnDate
	OPEN @CUR_CALSTAY

	FETCH @CUR_CALSTAY INTO @AttDate1, @AtOffice
		WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE @Attendance 
			SET Late = IsNull(CAST((DATEDIFF(MINUTE, LEFT(OfficeStartHour, 8), LEFT(InTime, 8))/60) as varchar) + '.' + REPLICATE('0', 2 - Len(((CAST(DATEDIFF(MINUTE, LEFT(OfficeStartHour, 8), LEFT(InTime, 8)) As int))%60)*100/60)) + CAST(((CAST(DATEDIFF(MINUTE, LEFT(OfficeStartHour, 8), LEFT(InTime, 8)) As int)%60)*100/60) as varchar), '--'),
			AtOffice = CASE ISNUMERIC(@AtOffice) WHEN 1 THEN CAST(@AtOffice AS float) ELSE 0.00 END,
			TotalWork = (CASE ISNUMERIC(@AtOffice) WHEN 1 THEN CAST(@AtOffice AS float) ELSE 0.00 END) + (CASE ISNUMERIC(OutOfficeVisit) WHEN 1 THEN CAST(OutOfficeVisit AS float) ELSE 0 END),
			OverTime = (((CASE ISNUMERIC(@AtOffice) WHEN 1 THEN CAST(@AtOffice AS float) ELSE 0.00 END) + (CASE ISNUMERIC(OutOfficeVisit) WHEN 1 THEN CAST(OutOfficeVisit AS float) ELSE 0 END)) - 8.00),
			OutOffice = (CAST(DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(OutTime, 8))/60 as varchar) + '.' + REPLICATE('0', 2 - Len(((CAST(DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(OutTime, 8)) As int))%60)*100/60)) + CAST(((CAST(DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(OutTime, 8)) As int)%60)*100/60) as varchar))
			WHERE AttendanceDate = @AttDate1
			FETCH @CUR_CALSTAY INTO @AttDate1, @AtOffice
		END
-- Insert Alternate Holiday and Other Holidays
	DECLARE	@i int
	SET @i = 0
	SET @Holiday1 = ''
	--SET @i = (SELECT COUNT(1) AttendanceDate FROM @Attendance WHERE AttendanceDate = @AttDateDBL)
	FETCH @CUR_ATTALTHDAY INTO @AttDate, @Holiday
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @AttDateDBL = @AttDate
			SET @Holiday2 = @Holiday
			FETCH @CUR_ATTALTHDAY INTO @AttDate, @Holiday
			IF @AttDateDBL <> @AttDate
				BEGIN
					IF CAST((SELECT AttendanceDate FROM @Attendance WHERE AttendanceDate = @AttDateDBL) AS smalldatetime) = @AttDateDBL
					--IF @i > 0
						BEGIN
							UPDATE @Attendance 
							SET Comments = @Holiday2
							WHERE AttendanceDate = @AttDateDBL
						END
					ELSE
						BEGIN
							INSERT INTO @Attendance
							(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, TotalWorkingDay)
							VALUES (@AttDateDBL, @EmployeeName, @EmployeeGroup, '', '--', '', 0.00, 0.00 , @OfficeStartHour, @WorkingHour, '0.00', @Holiday2 + @Holiday1, '', 0) 
							SET @Holiday2 = ''
							SET @Holiday1 = ''
						END
				END
			ELSE
				SET @Holiday1 = ', ' + @Holiday2
		END

	FETCH LAST FROM @CUR_ATTALTHDAY INTO @AttDate, @Holiday
	SET @i = (SELECT COUNT(1) FROM @Attendance WHERE AttendanceDate = @AttDate)
	IF @i=0
		INSERT INTO @Attendance
		(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, TotalWorkingDay)
		VALUES (@AttDate, @EmployeeName, @EmployeeGroup, '', '--', '', 0.00, 0.00 , @OfficeStartHour, @WorkingHour, '0.00', @Holiday, '', 0) 
	ELSE
		UPDATE @Attendance SET Comments = @Holiday	WHERE AttendanceDate = @AttDate
-- Insert Leave
	DECLARE		@ChkComents		varchar(100)
	SET	@ChkComents = ''
	SET	@ChkComents = (SELECT Comments FROM @Attendance WHERE AttendanceDate = @AttDate)
	FETCH @CUR_LEAVE INTO @AttDate, @Comments
		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF CAST((SELECT AttendanceDate FROM @Attendance WHERE AttendanceDate = @AttDate) AS smalldatetime) = @AttDate
				BEGIN
					IF @ChkComents <> @Comments
						BEGIN
							UPDATE @Attendance 
							SET Comments = Comments + ', ' + @Comments
							WHERE AttendanceDate = @AttDate And Comments <> @Comments
						END
				END
			ELSE
				BEGIN
					INSERT INTO @Attendance
					(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose, TotalWorkingDay)
					VALUES (@AttDate, @EmployeeName, @EmployeeGroup, '', '--', '', 0.00, 0.00 , @OfficeStartHour, @WorkingHour, '0.00', @Comments, '', 0)
				END
			FETCH @CUR_LEAVE INTO @AttDate, @Comments
		END

-- Total Working Day Count By Month
	IF CURSOR_STATUS('local','@CUR_TWDay')=1
		BEGIN
			CLOSE  @CUR_TWDay
			DEALLOCATE @CUR_TWDay
		END

	SET @CUR_TWDay = CURSOR STATIC FOR
	SELECT SUM(TotalWorkingDay), WorkingMonth FROM FN_WorkingDayCount (@StartDate, @EndDate, @EmployeeID)
	GROUP BY WorkingMonth
	ORDER BY WorkingMonth
	OPEN @CUR_TWDay
	
	FETCH @CUR_TWDay INTO @TotalWorkingDay, @WorkingMonth
	WHILE @@FETCH_STATUS = 0
		BEGIN
			UPDATE @Attendance 
			SET TotalWorkingDay = @TotalWorkingDay
			WHERE MONTH(AttendanceDate) = MONTH(@WorkingMonth) And Year(AttendanceDate) = Year(@WorkingMonth)
			FETCH @CUR_TWDay INTO @TotalWorkingDay, @WorkingMonth
		END

-- Insert Absent
	IF CURSOR_STATUS('local','@CUR_ABSENT')=1
		BEGIN
			CLOSE  @CUR_ABSENT
			DEALLOCATE @CUR_ABSENT
		END

	SET @CUR_ABSENT = CURSOR STATIC FOR
	SELECT AttendanceDate, Comments FROM @Attendance
	ORDER BY AttendanceDate
	OPEN @CUR_ABSENT

	FETCH @CUR_ABSENT INTO @AttDate, @Comments
	WHILE @@FETCH_STATUS = 0
		BEGIN
			WHILE(CAST(@StartDate As Date) <= CAST(@EndDate As Date))
				BEGIN
						IF CAST(@StartDate As Date) <> CAST(@AttDate As Date)
							INSERT INTO @Attendance
							(AttendanceDate, EmployeeName, EmployeeGroup, InTime, Late, OutTime, TotalWork, AtOffice, OfficeStartHour, WorkingHour, OutOfficeVisit, Comments, Purpose)
							VALUES (@StartDate, @EmployeeName, @EmployeeGroup, '', '--', '', 0.00, 0.00 , @OfficeStartHour, @WorkingHour, '0.00', 'Absent', '')
						ELSE
							FETCH NEXT FROM @CUR_ABSENT INTO @AttDate, @Comments
							
						SET @StartDate = DATEADD(day,1,@StartDate)
				END
		END
	RETURN
END
-----------------------------------------------------------------------------------------------
3rd Function
-----------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[FN_TotalStayTime]
(
	@StartDate As varchar(10),
	@EndDate As varchar(10),
	@EmployeeID As int
)
RETURNS @InOut TABLE(
		Id int PRIMARY KEY IDENTITY,
		AttnDate smalldatetime,
		InTime time(7) null,
		OutTime time(7) null,
		TotalStayTime VARCHAR(50) null
)
AS
BEGIN
	DECLARE	@TotalStayTime As varchar(5),
			@AttnDate as smalldatetime,
			@AttnDate1 as smalldatetime,
			@InTime As time(7),
			@OutTime As time(7),
			@InOutTime As time(7),
			@InOutTime1 As time(7),
			@Status As int,
			@Status1 As int,
			@AttID As int
	DECLARE @CUR_INOUT CURSOR
	DECLARE @CUR_INT CURSOR
	DECLARE @CUR_OUTT CURSOR
	DECLARE @CUR_CHECKSTAT CURSOR

	IF CURSOR_STATUS('local','@CUR_INOUT')=1
		BEGIN
			CLOSE  @CUR_INOUT
			DEALLOCATE @CUR_INOUT
		END

	SET @CUR_INOUT = CURSOR STATIC FOR
	select edate, etime, status from Information where edate BETWEEN @StartDate And @EndDate and id = @EmployeeID order by edate, etime, sn
	OPEN @CUR_INOUT

	DECLARE @TMPInOut TABLE(
			Id int PRIMARY KEY IDENTITY,
			AttnDate smalldatetime,
			InOutTime time(7) null,
			Status int null
	)

	DECLARE @Counter int,
			@Skip int
	SET @Counter = 0
	SET @Skip = 0

	FETCH @CUR_INOUT INTO @AttnDate, @InOutTime, @Status
	WHILE @Status <> 1
		FETCH NEXT FROM @CUR_INOUT INTO @AttnDate, @InOutTime, @Status
		
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @AttnDate1 = @AttnDate
			SET @InOutTime1 = @InOutTime
			SET @Status1 = @Status
			FETCH @CUR_INOUT INTO @AttnDate, @InOutTime, @Status
			IF @Status1 <> @Status
				BEGIN
					IF @Skip <> @Status1
						BEGIN
							INSERT INTO @TMPInOut(AttnDate, InOutTime, Status)
							VALUES (@AttnDate1, @InOutTime1, @Status1)
							SET @Counter = 0
							SET @Skip = @Status1
						END
				END
			ELSE
				BEGIN
					IF @Status = 0
						BEGIN
						IF @Counter = 0
							BEGIN
								INSERT INTO @TMPInOut(AttnDate, InOutTime, Status)
								VALUES (@AttnDate1, @InOutTime1, @Status1)
								SET @Counter = @Counter + 1
								SET @Skip = @Status1
							END
						FETCH @CUR_INOUT INTO @AttnDate, @InOutTime, @Status
						END
				END
		END
	DECLARE	@i int
	FETCH LAST FROM @CUR_INOUT INTO @AttnDate, @InOutTime, @Status
	--SET @Status1 = (SELECT Status FROM @TMPInOut WHERE ID = (SELECT MAX(ID) FROM @TMPInOut))
	SET @i = (SELECT COUNT(1) FROM @TMPInOut WHERE ID = (SELECT MAX(ID) FROM @TMPInOut))
	--IF @Status <> @Status1
	IF @i=0
		INSERT INTO @TMPInOut(AttnDate, InOutTime, Status) VALUES (@AttnDate, @InOutTime, @Status)

	IF CURSOR_STATUS('local','@CUR_CHECKSTAT')=1
		BEGIN
			CLOSE  @CUR_CHECKSTAT
			DEALLOCATE @CUR_CHECKSTAT
		END

	SET @CUR_CHECKSTAT = CURSOR STATIC FOR
	SELECT ID, Status FROM @TMPInOut ORDER BY ID
	OPEN @CUR_CHECKSTAT

	FETCH LAST FROM @CUR_CHECKSTAT INTO @AttID, @Status
	DELETE @TMPInOut WHERE ID = @AttID And Status = 1

	IF CURSOR_STATUS('local','@CUR_INT')=1
		BEGIN
			CLOSE  @CUR_INT
			DEALLOCATE @CUR_INT
		END

	SET @CUR_INT = CURSOR STATIC FOR
	SELECT AttnDate, CAST(LEFT(InOutTime, 8) As time(7)) As InTime 
	FROM @TMPInOut 
	WHERE AttnDate BETWEEN @StartDate And @EndDate And status = 1
	ORDER BY AttnDate, InOutTime

	OPEN @CUR_INT

	IF CURSOR_STATUS('local','@CUR_OUTT')=1
		BEGIN
			CLOSE  @CUR_OUTT
			DEALLOCATE @CUR_OUTT
		END

	SET @CUR_OUTT = CURSOR STATIC FOR
	SELECT AttnDate, CAST(LEFT(InOutTime, 8) As time(7)) As InTime 
	FROM @TMPInOut 
	WHERE AttnDate BETWEEN @StartDate And @EndDate And status = 0
	ORDER BY AttnDate, InOutTime
	OPEN @CUR_OUTT

	DECLARE @Id int
	SET @Id = 1

	FETCH @CUR_INT INTO @AttnDate, @InTime
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @InOut(AttnDate, InTime)
			VALUES (@AttnDate, @InTime)
			FETCH @CUR_INT INTO @AttnDate, @InTime
		END

	FETCH @CUR_OUTT INTO @AttnDate, @OutTime
	WHILE @@FETCH_STATUS = 0
	BEGIN
		UPDATE @InOut 
		SET OutTime = @OutTime,
		TotalStayTime = CASE ISNUMERIC(DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8))) WHEN 1 THEN CAST((DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8))) AS varchar) ELSE 0 END	--DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8))	--REPLICATE('0', 2 - Len((DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8)))/60)) + cast ((DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8)))/60 as varchar) + '.' + REPLICATE('0', 2 - Len((DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8)))%60)) + cast ((DATEDIFF(MINUTE, LEFT(InTime, 8), LEFT(@OutTime, 8)))%60 as varchar)
		Where id=@id
		FETCH @CUR_OUTT INTO @AttnDate, @OutTime
		Set @Id = @Id+1
	END
	RETURN
END
-----------------------------------------------------------------------------------------------
4th Function
-----------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[FN_WorkingDayCount]
(
		@StartDate varchar(11),
		@EndDate varchar(11),
		@EmployeeID int
)
RETURNS @WDC TABLE(
		TotalWorkingDay		INT				NULL,
		WorkingMonth		smalldatetime	NULL
)
AS
BEGIN
	
	DECLARE @TotalWorkingDay		AS INT,
			@WorkingMonth			AS smalldatetime
	DECLARE @CUR_TWD CURSOR
	
	IF CURSOR_STATUS('local','@CUR_TWD')=1
		BEGIN
			CLOSE  @CUR_TWD
			DEALLOCATE @CUR_TWD
		END

	SET @CUR_TWD = CURSOR STATIC FOR
	SELECT COUNT(DISTINCT WorkingDay) As TWorkingDay, CAST((CAST(MONTH(WorkingDay) As varchar(2)) + '/1/' + CAST(YEAR(WorkingDay) As varchar(4))) As DATE) As WorkingMonth
	FROM TotalWorkingDay WHERE WorkingDay BETWEEN @StartDate And @EndDate
	GROUP BY MONTH(WorkingDay), YEAR(WorkingDay)
	ORDER BY MONTH(WorkingDay), YEAR(WorkingDay)
/*
--	COUNT TOTAL LEAVE PER MONTH
	SELECT COUNT(LD.LeaveDate) As TotalWorkingDay, CAST((CAST(MONTH(LD.LeaveDate) As varchar(2)) + '/1/' + CAST(YEAR(LD.LeaveDate) As varchar(4))) As DATE) As WorkingMonth
	FROM LeaveReason AS LR, LeaveDetails AS LD 
	WHERE LR.LeaveID = LD.LeaveID And LR.EID = @EmployeeID AND LD.LeaveDate BETWEEN @StartDate And @EndDate
	And (LD.LeaveDate NOT IN(SELECT OC.CalDate As TotalWorkingDay FROM OffCalender As OC, Holiday As H WHERE H.Id = OC.holidayId And (OC.ImplementedOn = (SELECT empGroup FROM Personal WHERE PID = 149)) 
	And (CalDate BETWEEN @StartDate And @EndDate) And (OC.calDate NOT IN(SELECT DISTINCT EDate FROM Information WHERE EDate BETWEEN @StartDate And @EndDate And ID = @EmployeeID))))
	GROUP BY MONTH(LD.LeaveDate), YEAR(LD.LeaveDate)
	Union All
--	COUNT TOTAL ATTENDANCE PER MONTH
	SELECT DISTINCT COUNT(DISTINCT EDate) As TotalWorkingDay, CAST((CAST(MONTH(EDate) As varchar(2)) + '/1/' + CAST(YEAR(EDate) As varchar(4))) As DATE) As WorkingMonth
	FROM Information
	WHERE (EDate BETWEEN @StartDate And @EndDate) And id = @EmployeeID
	GROUP BY MONTH(EDate), YEAR(EDate)
	UNION ALL
--	COUNT TOTAL ALTERNATE HOLIDAY PER MONTH
	SELECT COUNT(OC.CalDate) As TotalWorkingDay, CAST((CAST(MONTH(OC.CalDate) As varchar(2)) + '/1/' + CAST(YEAR(OC.CalDate) As varchar(4))) As DATE) As WorkingMonth
	FROM OffCalender As OC, Holiday As H
	WHERE H.Id = OC.holidayId And (OC.ImplementedOn = (SELECT empGroup FROM Personal WHERE PID = @EmployeeID)) 
	And (CalDate BETWEEN @StartDate And @EndDate) And (OC.calDate NOT IN(SELECT DISTINCT EDate FROM Information WHERE EDate BETWEEN @StartDate And @EndDate And ID = @EmployeeID))
	GROUP BY MONTH(OC.CalDate), YEAR(OC.CalDate)
*/
	OPEN @CUR_TWD
	
	FETCH @CUR_TWD INTO @TotalWorkingDay, @WorkingMonth
	WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @WDC
			(TotalWorkingDay, WorkingMonth)
			VALUES (@TotalWorkingDay, @WorkingMonth)
			FETCH @CUR_TWD INTO @TotalWorkingDay, @WorkingMonth
		END
		
	RETURN
END