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>>
CREATE TABLE [dbo].[Claims]( > [clm_pk] [uniqueidentifier] NOT NULL, > [clm_forwarded] [date] NULL, > [clm_clifk] [uniqueidentifier] NOT NULL >)>
CREATE TABLE [dbo].[ClaimItems]( > [Cit_pk] [uniqueidentifier] NOT NULL, > [cit_settled] [date] NULL, > [cit_received] [date] NULL, > [cit_clmfk] [uniqueidentifier] NOT NULL >)>
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 >)>
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>
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>
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>