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>>