ChewDoggie
Programmer
Hello All,
I've written a stored procedure that almost works.....I think.
I use "FETCH NEXT FROM <cursor>" syntax b/c I don't know of another way to accomplish what I want.
THe problem is that when I execute the "DECLARE AssocHours_Cursor CURSOR FOR SELECT..." statement, the "SET @HrsRows = @@ROWCOUNT" statement only returns ONE row. But the same statement in SSMS returns 19 rows.
Here's my code:
99% of my SP experience is with simple queries and updates. This is my second attempt at something that involves significant overhead.
If there's a better way, lay it on me.
Thanks !
Chew
10% of your life is what happens to you. 90% of your life is how you deal with it.
I've written a stored procedure that almost works.....I think.
I use "FETCH NEXT FROM <cursor>" syntax b/c I don't know of another way to accomplish what I want.
THe problem is that when I execute the "DECLARE AssocHours_Cursor CURSOR FOR SELECT..." statement, the "SET @HrsRows = @@ROWCOUNT" statement only returns ONE row. But the same statement in SSMS returns 19 rows.
Here's my code:
Code:
USE [HRConnect]
GO
/****** Object: StoredProcedure [dbo].[Attendances_GetForAssociate1] Script Date: 01/05/2010 15:43:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: ChewDOGGIE
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[Attendances_GetForAssociate1]
-- Add the parameters for the stored procedure here
@inAssociateGUID UniqueIdentifier
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @AH_SD date
DECLARE @AH_ED date
DECLARE @AH_TH decimal(6,2)
DECLARE @AH_CH decimal(6,2)
DECLARE @DD_SD date
DECLARE @DD_ED date
DECLARE @DD Integer
DECLARE @HrsRows Integer
DECLARE @DiffRows Integer
DECLARE @AddDays Integer
DECLARE @RowNum Integer
DECLARE @CalcHours Decimal(6,2)
DECLARE @DropDate date
DECLARE @Points decimal(6,2)
DECLARE @AttGUID uniqueidentifier
DECLARE @RGUID uniqueidentifier
DECLARE @Comments nvarchar(60)
DECLARE @Reason nvarchar(60)
-- Insert statements for procedure here
CREATE TABLE #OutPut (StartDate date, EndDate date, TotalHours decimal(6,2), CalculatedHours decimal(6,2), DropOffDate date, AttendanceGUID uniqueidentifier, AssociateGUID uniqueidentifier, ReasonCode nvarchar(30), ReasonGUID uniqueidentifier, Comments nvarchar(50))
DECLARE AssocHours_Cursor CURSOR FOR SELECT AttendanceGUID, AttendanceStartDate, AttendanceEndDate, TotalHours, CalculatedHours, Comments, R.ReasonGUID, R.ReasonCode from tblAttendance INNER JOIN tblReasons as R on tblAttendance.ReasonGUID = R.ReasonGUID WHERE AssociateGUID = @inAssociateGUID and TotalHours > 0 order by AttendanceStartDate
SET @HrsRows = @@ROWCOUNT
DECLARE DiffDates_Cursor CURSOR FOR SELECT AttendanceStartDate, AttendanceEndDate, DATEDIFF("D", AttendanceStartDate, AttendanceEndDate) AS DiffDays FROM tblAttendance WHERE AssociateGUID = @inAssociateGUID and DATEDIFF("D", AttendanceStartDate, AttendanceEndDate) > 5 ORDER BY AttendanceStartDate
SET @DiffRows = @@ROWCOUNT
OPEN AssocHours_Cursor
OPEN DiffDates_Cursor
While @HrsRows > 0
BEGIN
FETCH NEXT FROM AssocHours_Cursor INTO @AttGUID, @AH_SD, @AH_ED, @AH_TH, @AH_CH, @Comments, @RGUID, @Reason
SET @AddDays = 364
SET @RowNum = 0
while @RowNum <= @DiffRows
BEGIN
FETCH NEXT FROM DiffDates_Cursor INTO @DD_SD, @DD_ED, @DD
IF (@DD_SD > @AH_SD AND @DD_SD <= DATEADD("D", @DD, @AH_SD))
BEGIN
SET @AddDays = @AddDays + @DD
END
SET @RowNum = @RowNum + 1
END
SET @CalcHours = 0
IF (DATEDIFF("D", GETDATE(), @AH_SD) > @AddDays)
SET @CalcHours = @AH_TH
IF (@CalcHours = 0)
SET @Points = 0
ELSE IF (@CalcHours < 2)
SET @Points = 0.25
ELSE IF (@CalcHours < 4)
SET @Points = 0.50
ELSE IF (@CalcHours < 6)
SET @Points = 0.75
ELSE IF (@CalcHours < 7.99)
SET @Points = 1
ELSE
SET @Points = @CalcHours / (8 * 1)
SET @DropDate = DATEADD("D", @AddDays, @AH_SD)
--update tblAttendance set AttendanceStartDate = @AH_SD, AttendanceEndDate = @AH_ED,
INSERT INTO #OutPut VALUES(@AH_SD, @AH_ED, @AH_TH, @CalcHours, @DropDate, @AttGUID, @inAssociateGUID, @Reason, @RGUID, @Comments)
SET @HrsRows = @HrsRows - 1
END
SELECT * FROM #OutPut
END99% of my SP experience is with simple queries and updates. This is my second attempt at something that involves significant overhead.
If there's a better way, lay it on me.
Thanks !
Chew
10% of your life is what happens to you. 90% of your life is how you deal with it.