>>Thanks,
>>
>>can you provide me with some instructions as to how to go about this, please?
>>
>>>You can have either a calculated field in a table or perform calculations in a stored procedure. If you go the second route, then I suggest to have Calendar table in your database. Having this Calendar table (that will put IsHoliday or IsWeekend for Holidays and Weekends) will make the calculations of Working days trivial.
>>>
>About adding Calendar table?
>
>If so, check
>http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/passing-multiple-ranges-to-stored-proced
>
>I gave the script there.
>
>Once you added the table, see, if you will be able to calculate the number of days. If you still will have problems, post back DDL and insert statements, I'll try to come up with the full solution.

Hi Naomi,

I've created the Numbers table and the Calendar table along with functions to GetWorkingDays and ClaimItemStatus. Now I'm stuck on the last function to get the ClaimStatus based on these rules:

1. If any Claim Items are Overdue, then Claim is Overdue
2. Else If all Claim Items are Settled, then Claim is Settled
3. Otherwise Status is Pending

Here is what I have attempted:

CREATE FUNCTION dbo.ClaimStatus
( 
    @Clm_PK UNIQUEIDENTIFIER
) 
RETURNS INT 
AS 
BEGIN
	DECLARE @Status INT
	DECLARE @DayLimit INT
	DECLARE @Cli_PK UNIQUEIDENTIFIER
	
	SET @Status = 1 /* Pending */
	SET @Cli_PK = (SELECT clm_clifk FROM Claims WHERE clm_pk = @Cli_PK)
	
	SET @DayLimit = (SELECT com_OverDueDays 
					FROM Companies 
						INNER JOIN Clients ON com_pk = cli_comfk
							WHERE cli_comfk = @Cli_PK)
							
	SELECT CIT_PK
			,dbo.GetClaimItemStatus(clm_Forwarded, cit_received, @DayLimit)
		FROM ClaimItems	
			INNER JOIN Claims ON cit_clmfk = clm_pk
			WHERE cit_clmfk = @Clm_PK
			
    RETURN @Status 
END 
GO

The problem I have is that I get Error Msg 444: Select statements included within a function cannot return data to a client which is due to my SELECT CIT_PK...

How do I return that into a temporary table/cursor so that I can check the status of the claim items?