查询超时已过期 [英] query timeout expired
问题描述
请任何人帮助我。我有SQL函数。在这个函数里面我使用了一些其他函数,并使用了一些CURSOR到FETCH记录并将这个值插入临时表。
最后当我在VB6中执行这个函数时.0应用程序在每天早上我的数据库服务器开启时,它会显示此类型的消息。当我在SQL Server查询分析器中执行此功能时,之后再次从我的VB6.0应用程序执行此功能,然后它正常工作。
我无法理解有什么问题。
Bipul
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
推荐答案
if u Increase the time out time Given in your application... this issue can be solved....i went through the same issue...
if u Increase the time out time Given in your application... this issue can be solved....i went through the same issue...
这篇关于查询超时已过期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!