>>FWIW, I got this to work:
>>
>>

select 1 
>>	from ClaimItems inner join Claims on cit_clmfk = clm_pk
>>	where dbo.claimitemstatus(clm_forwarded
>>				,cit_received
>>				,(select com_overduedays
>>				from companies
>>					inner join Clients on cli_comfk = com_pk
>>					inner join Claims subclaims on clm_clifk = cli_pk
>>				where subclaims.clm_pk = claims.clm_pk)) = 3

>
>
>This is absolutely not readable query.
>
>I was trying to fix your query in the meantime (when you replied) using CROSS APPLY, but I think your query should be a simple query with JOINS and then select with the restriction from derived table. Can you please explain this query in English, I'll write a better version?
>
>BTW, using Scalar functions in WHERE clause will make your query suffer from performance.

This is all part of what we started discussing yesterday.

I have a Claims table (I have cut out fields that do not affect this):

CREATE TABLE [dbo].[Claims](
	[clm_pk] [uniqueidentifier] NOT NULL,
	[clm_forwarded] [date] NULL,
	[clm_clifk] [uniqueidentifier] NOT NULL
)

and a ClaimItems table:

CREATE TABLE [dbo].[ClaimItems](
	[Cit_pk] [uniqueidentifier] NOT NULL,
	[cit_settled] [date] NULL,
	[cit_received] [date] NULL,
	[cit_clmfk] [uniqueidentifier] NOT NULL
)

and a Clients table:

CREATE TABLE [dbo].[Clients](
	[cli_pk] [uniqueidentifier] NOT NULL,
	[cli_name] [nvarchar](50) NOT NULL,
	[cli_comfk] [uniqueidentifier] NOT NULL
)<pre>

and a Companies table:

<pre>CREATE TABLE [dbo].[Companies](
	[com_name] [nchar](50) NOT NULL,
	[com_pk] [uniqueidentifier] NOT NULL,
	[com_overduedays] [smallint] NULL
)

One Claim can have one or many Claim Items.

One Client can have none or many Claims.

One Company can have none or many Clients.

Each Claim Item has a status which is either 1 - Pending, 2 - Settled or 3 - Overdue which depends on when the Claim was forwarded to the Company and if a Settlement has been Received within the OverDueDays limit set by the Company.

Each Claim will also have a Status which depends on the status of all its Claim Items: if any ClaimItems are Overdue, then Claim is Overdue, if not then if there is a Pending Claim Item, then Claim Status is Pending, otherwise, Claim is considered Settled.

So I have these functions:

USE [GFLClaims]
GO
/****** Object:  UserDefinedFunction [dbo].[ClaimItemStatus]    Script Date: 07/15/2011 10:46:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ClaimItemStatus]
( 
    @Start Date,
    @End Date,
    @DayLimit Int
) 
RETURNS INT 
AS 
BEGIN
	DECLARE @Status INT
	DECLARE @LapsedDays INT
    SET @Status = 1

    /* if no date forwarded then Pending */
    IF NOT @Start IS NULL
    
		/* if end date present, then Settled */
		IF NOT @End IS NULL
			SET @Status = 2 /* Settled */
		ELSE
			/* otherwise check if exceeded company limit of days lapsed */
			BEGIN
				SET @LapsedDays = dbo.GetWorkingDays(@Start, GETDATE())
				IF @LapsedDays > @DayLimit
					SET @Status = 3
			END

    RETURN @Status 
END

and

USE [GFLClaims]
GO
/****** Object:  UserDefinedFunction [dbo].[GetWorkingDays]    Script Date: 07/15/2011 10:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetWorkingDays] 
( 
    @Start Datetime,
    @End DateTime 
) 
RETURNS INT 
AS 
BEGIN 
    RETURN (SELECT COUNT(*) FROM dbo.Calendar 
    WHERE isWeekday = 1 
    AND isHoliday = 0 
    AND dt >= @Start AND dt < @End ) 
END

Then I can get the Claim Items and there status like this:

select cit_pk, clm_pk, clm_forwarded, cit_received, dbo.claimitemstatus(clm_forwarded, cit_received, (select com_overduedays
from companies
	inner join Clients on cli_comfk = com_pk
	inner join Claims subclaims on subclaims.clm_clifk = cli_pk
	where subclaims.clm_pk = claims.clm_pk)) as statusfk
from ClaimItems inner join Claims on cit_clmfk = clm_pk

So now I'm trying to get the Status of the Claims.