USE [SIAS] >GO >/****** Object: UserDefinedFunction [dbo].[GetNextPremiumDue] Script Date: 02/21/2011 09:42:08 ******/ >SET ANSI_NULLS ON >GO >SET QUOTED_IDENTIFIER ON >GO >-- ============================================= >-- Author: <Author,,Name> >-- Create date: <Create Date, ,> >-- Description: <Description, ,> >-- ============================================= >CREATE FUNCTION [dbo].[GetNextPremiumDue] >( > -- Add the parameters for the function here > @PolicyHeaderPK uniqueidentifier >) >RETURNS datetime >AS >BEGIN > -- Declare the return variable here > DECLARE @PremiumDue datetime > > -- Add the T-SQL statements to compute the return value here > DECLARE @DatePaidTo datetime > declare @MonthsToAdd int > declare @PremiumMonths int > declare @IssueDate datetime > > SELECT @MonthsToAdd = SUM( > CASE [PremiumMode] > WHEN 'Monthly' THEN [ReceiptDetails].[NumberOfPremiums] * 1 > WHEN 'Quarterly' THEN [ReceiptDetails].[NumberOfPremiums] * 3 > WHEN 'Semi-Annually' THEN [ReceiptDetails].[NumberOfPremiums] * 6 > WHEN 'Annually' THEN [ReceiptDetails].[NumberOfPremiums] * 12 > ELSE 0 > END ) > FROM [dbo].[ReceiptDetails] > inner join [dbo].[Receipts] on [ReceiptDetails].[ReceiptFK] = [Receipts].[ReceiptPK] > WHERE > ([Receipts].[PolicyHeaderFK] = @PolicyHeaderPK > AND ReceiptTypeFK = 3) > > SELECT @IssueDate = [Policies].[IssueDate], > @PremiumMonths = > CASE [Policies].[PremiumMode] > WHEN 'Monthly' THEN 1 > WHEN 'Quarterly' THEN 3 > WHEN 'Semi-Annually' THEN 6 > WHEN 'Annually' THEN 12 > ELSE 0 > END > FROM [dbo].[Policyheaders] > INNER JOIN [dbo].[Policies] ON [Policyheaders].[PolicyHeaderPK] = [Policies].[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 > WHERE > ([Policyheaders].[PolicyHeaderPK] = @PolicyHeaderPK) > > SET @DatePaidTo = ISNULL(DATEADD(M, @MonthsToAdd, @IssueDate), @IssueDate) > SET @PremiumDue = DATEADD(M, @PremiumMonths, @DatePaidTo) > > -- Return the result of the function > RETURN @PremiumDue > >END >>
SET @DatePaidTo = ISNULL(DATEADD(M, @MonthsToAdd, @IssueDate), @IssueDate)>