simple function help

[ChewDoggie]

Hello All,

I'm trying to create a function, (I believe I want a scalar-function). It queries a table, performs calculations and then returns the result as an decimal.

Here's the code:

USE [MySampleDatabase]
GO
/****** Object:  UserDefinedFunction [dbo].[GetCalculatedHours]    Script Date: 11/23/2009 12:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Chewdoggie
-- Create date: 11/23/2009
-- Description:	
-- =============================================
ALTER FUNCTION [dbo].[GetCalculatedHours] 
(
	-- Add the parameters for the function here
	@inAttendanceEndDate		Date,
	@inAssociateGUID			UniqueIdentifier,
	@inTotalHours				Decimal(6,2)
)
RETURNS Decimal(6,2)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Switch			Integer
	DECLARE @DaysDiff		Integer
	DECLARE @Days			Integer
	DECLARE @CalcHours		Integer
	DECLARE @SD				Date
	DECLARE @ED				Date

	SET @Switch = 0
	SET @DaysDiff = 0
	DECLARE dates_cursor CURSOR FOR	SELECT AttendanceStartDate, AttendanceEndDate FROM tblAttendance as Att INNER JOIN tblReasons as R on R.ReasonGUID = Att.ReasonGUID where Att.AttendanceEndDate >= DATEADD(DAY, -365, GetDate()) AND Att.AttendanceEndDate <> Att.AttendanceStartDate and R.BonusEffect < 2 and Att.AssociateGUID = @inAssociateGUID and Att.TotalHours > 0.00
	OPEN dates_cursor
	FETCH NEXT FROM dates_cursor into @SD, @ED
	SET @DaysDiff = DATEDIFF(DAY, @SD, @ED) + 1
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SELECT @SD as AttendanceStartDate, @ED as AttendanceEndDate
			FETCH NEXT FROM dates_cursor into @SD, @ED
			SET @DaysDiff += DATEDIFF(DAY, @SD, @ED) + 1
			--SET @DaysDiff += DATEDIFF(DAY, Att.AttendanceStartDate, Att.AttendanceEndDate)
		END

	SET @CalcHours = 0
	IF @Days <= (@DaysDiff + 365)
		SET @CalcHours = @Days * 8

	-- Return the result of the function
	RETURN @CalcHours
END

This query will produce several rows of DATA. I want to loop through the rows, perform calculations on the two dates and then return the calculated DATA as a Decimal. It keeps barfing with the error "Select statements included within a function cannot return data to a client". Interesting as I'm not trying to return data from a select statement.

Anyway, any assistance would be appreciated.

Chew


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[bborissov]

date?
Are you working with 2008?
Also that row is what?
....
SELECT @SD as AttendanceStartDate,
@ED as AttendanceEndDate
......
?
You need only this:

   SET @DaysDiff = 0
   FETCH NEXT FROM dates_cursor into @SD, @ED
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @DaysDiff = @DaysDiff + DATEDIFF(DAY, @SD, @ED) + 1
            FETCH NEXT FROM dates_cursor into @SD, @ED
        END

Also you didn't close and release the cursor at the end. YOU SHOULD!

And finally you didn't need a cursor at all

SET @DaysDiff = 0
SELECT @DaysDiff = @DaysDiff + 
                   DATEDIFF(DAY, AttendanceStartDate, AttendanceEndDate) + 1
FROM tblAttendance as Att 
INNER JOIN tblReasons as R ON
           R.ReasonGUID = Att.ReasonGUID
WHERE Att.AttendanceEndDate >= DATEADD(DAY, -365, GetDate()) AND
      Att.AttendanceEndDate <> Att.AttendanceStartDate AND 
      R.BonusEffect < 2 AND
      Att.AssociateGUID = @inAssociateGUID AND
      Att.TotalHours > 0.00

 IF @Days <= (@DaysDiff + 365) -- From where these @DAYS comes from?
        SET @CalcHours = @Days * 8

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ChewDoggie]

Boris,

thank you...you started me in the right direction. Will report back tomorrow with more.

Thanks again.

Chew

10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[ChewDoggie]

As usual, I get into work and get pulled in a hundred different directions.

I **FINALLY** plugged your "non-cursor" code into my Function and, with only a few tweaks, worked like a charm.

Thanks Boris !

Chew


10% of your life is what happens to you. 90% of your life is how you deal with it.

 

[JeffModen]

If you tweek Boris' code just a little more, you probably don't need the function, either.

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row