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 @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 @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
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 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)
) 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
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
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)
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
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
DECLARE @i int
SET @i = 0
SET @Holiday1 = ''
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
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
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
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
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 @i = (SELECT COUNT(1) FROM @TMPInOut WHERE ID = (SELECT MAX(ID) FROM @TMPInOut))
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
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)
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