SET ANSI_NULLS ON >GO >SET QUOTED_IDENTIFIER ON >GO >-- ============================================= >-- Author: <Author,,Name> >-- Create date: <Create Date, ,> >-- Description: <Description, ,> >-- ============================================= >CREATE FUNCTION GetDatePaidTo >( > -- Add the parameters for the function here > @PolicyHeaderPK uniqueidentifier >) >RETURNS datetime >AS >BEGIN > -- Declare the return variable here > DECLARE @DatePaidTo datetime > > -- Add the T-SQL statements to compute the return value here > declare @MonthsToAdd int > declare @PremiumMode varchar(15) > declare @IssueDate datetime > > SELECT @MonthsToAdd = SUM( > CASE [PremiumMode] -- Add table alias here > WHEN 'Monthly' THEN NumberOfPremiums * 1 -- add table alias for NumberOfPremiums > WHEN 'Quarterly' THEN NumberOfPremiums * 3 > WHEN 'Semi-Annually' THEN NumberOfPremiums * 6 > WHEN 'Annually' THEN NumberOfPremiums * 12 > ELSE 0 > END ) > FROM [dbo].[ReceiptDetails] > inner join [dbo].[Receipts] on ReceiptFK = ReceiptPK -- looks like this join is not needed, also, add aliases to tables > WHERE > ([PolicyHeaderFK] = @PolicyHeaderPK > AND ReceiptTypeFK = 3) > > SELECT @IssueDate = IssueDate -- add alias here > FROM [dbo].[Policyheaders] > INNER JOIN [dbo].[Policies] ON [PolicyHeaderPK] = [PolicyHeaderFK] > INNER JOIN (SELECT [PolicyHeaderFK], MAX(LastChanged) as LastChanged > FROM [dbo].[Policies] > GROUP BY [PolicyHeaderFK]) subquery ON [Policies].[PolicyHeaderFk] = subquery.policyHeaderFK AND Policies.LastChanged = subquery.LastChanged > inner join [dbo].[Plans] on [PlanFK] = [PlanPK] -- do we need all these joins? > inner join [dbo].[Clients] on [ClientFK] = [ClientPK] > INNER JOIN [dbo].[Status] on [StatusFK] = [StatusPK] > WHERE > ([PolicyHeaderPK] = @PolicyHeaderPK) > > SET @DatePaidTo = DATEADD(M, @MonthsToAdd, @IssueDate) > > -- Return the result of the function > RETURN @DatePaidTo > >END >GO>