-- here is a simplified version
CREATE FUNCTION GetDataUDF
(
@QuoteID int = null
)
RETURNS @PersAutoCompanyComparison TABLE
(
	QuoteID int
	,Premium float
)

AS
BEGIN
-- completx query that returns a premium value and a quoteID
RETURN
END

SELECT 
	a.QuoteID
	,b.Premium
FROM
	Quotes a INNER JOIN GetDataUDF(null) b ON a.QuoteID = b.QuoteID
WHERE
	a.StartDate <= @StartDate
	AND a.EndDate >= @EndDate

SELECT 
	a.QuoteID
	,b.Premium
FROM
	Quotes a INNER JOIN GetDataUDF(a.QuoteID) b ON a.QuoteID = b.QuoteID
WHERE
	a.StartDate <= @StartDate
	AND a.EndDate >= @EndDate

OR

SELECT 
	a.QuoteID
	,(SELECT Premium FROM GetDataUDF(a.QuoteID)) AS Premium
FROM
	Quotes a
WHERE
	a.StartDate <= @StartDate
	AND a.EndDate >= @EndDate

CREATE FUNCTION GetPremium(@QuoteID int)
RETURNS Float
AS
BEGIN
 ....
 RETURN (@SomeFloatValue)
END

 Select QuoteID, dbo.Premium(QuoteID) as Premium
 From YourTable
 where ...

CREATE FUNCTION GetPersAutoQuoteCompanyComparisonData
-- this udf returns a company comparison table for a given quoteID.  This sp is broken into five steps
-- 1st step: retrieves all premium data and performs base calculations
-- 2nd step: retrieves all underwriting messages
-- 3rd step combines premium and message records
-- 4nd step: performs additional calculations based on base calculations
-- 5rd step: sets sort order based on input parameter
-- This dataSet is primarily used on the quoteComparison page, but is also used on the quote details page
(
@QuoteID int = null	-- if provide, returns only one quote result (null when result used in table joins)
,@CompanyID int = null  	-- if provided, returns only one record for a specific company
)
RETURNS @PersAutoCompanyComparison TABLE
(
	QuoteID int
	,CompanyID int
	,Company varchar(200)
	,Premium float
	,Down float
	,Payments float
	,Installments float
	,Liability float
	,Physical float
	,PremiumMessage varchar(100)
	,Message varchar(2000)
	,PaymentPlanDisplay varchar(100)
	,PaymentPlanCode varchar(50)
	,PaymentPlanID int
	,EFT varchar(1)
	,DownPayPercent float
	,InstallmentFee float
	,SelectedCompany int
)

AS
BEGIN
-- step 5: sort order
INSERT @PersAutoCompanyComparison 
SELECT
	QuoteID
	,CompanyID
	,Company
	,Premium
	,Down
	,Payments
	,Installments
	,Liability
	,Physical
	,PremiumMessage
	,Message
	,PaymentPlanDisplay 	-- needed on app
	,PaymentPlanCode	-- needed on app
	,PaymentPlanID		-- needed on quoteSheet
	,EFT			-- needed on quoteSheet
	,DownPayPercent 	-- needed on quoteSheet
	,InstallmentFee		-- needed on quoteSheet
	,SelectedCompany
FROM 
( 
	-- step 4: preform final calculations on premium records
	-- note: when no premium (message exists), set sortable payments column to 9999999 (message records sorted at the end)
	SELECT
		QuoteID
		,CompanyID
		,dbo.GetCompanyName(QuoteID, CompanyID) AS Company -- call udf to get complete company name
		,CASE -- calculate premium
			WHEN Premium = 0 THEN 9999999
			ELSE Premium + PolicyFee + FilingFee + StateFee + MembershipFee
		END AS Premium
		,CASE -- calculate downpayment
			WHEN Premium = 0 THEN 9999999
			WHEN DownPayPercent = 0 THEN 0 -- WUIC has 0 down payment plans, shouldn't impact anyone else
			ELSE Premium * DownPayPercent + PolicyFee + FilingFee + StateFee + MembershipFee
		END AS Down
		,CASE --calcuate payments based on premium and downpayment: (premium - down)/installments + installmentFee. (policy/filing/state fees in both premium and down calcs, cancel each other out)
			WHEN Installments = 0 THEN 0
			WHEN Premium = 0 THEN 9999999
			WHEN DownPayPercent = 0 THEN (Premium + PolicyFee + FilingFee + StateFee + MembershipFee)/Installments + InstallmentFee  -- WUIC has 0 down payment plans, shouldn't impact anyone else
			ELSE (Premium - Premium * DownPayPercent)/Installments + InstallmentFee
		END AS Payments 
		,Installments
		,Liability
		,Physical
		,PremiumMessage
		,PaymentPlanID
		,PaymentPlanCode
		,PaymentPlanDisplay
		,EFT
		,DownPayPercent
		,InstallmentFee
		,Message
		,SelectedCompany
	FROM
	(
		-- step3: combine all premium and message records 
		-- note: when sub2.Message exists, set sortable premium columns to 9999999 (message records sorted at the end)
		SELECT 
			Sub1.QuoteID
			,Coalesce(Sub1.CompanyID,Sub2.CompanyID) AS CompanyID
			,Coalesce(Sub1.Premium,0) AS Premium
			,Sub1.PolicyFee
			,Sub1.FilingFee
			,(Sub1.Premium * Sub1.StateFeeFactor) AS StateFee -- statefee is total premium * factor
			,Sub1.Installments
			,Sub1.InstallmentFee
			,Sub1.MembershipFee
			,Sub1.DownPayPercent
			,Sub1.PremiumMessage
			,Sub1.PaymentPlanID
			,Sub1.PaymentPlanCode
			,Sub1.PaymentPlanDisplay
			,Sub1.EFT
			,Sub2.Message
			,CASE
				WHEN Sub2.Message != null THEN 9999999
				ELSE Sub1.Liability
			END AS Liability
			,CASE
				WHEN Sub2.Message != null THEN 9999999
				ELSE Sub1.Physical
			END AS Physical
			,Sub1.SelectedCompany
		FROM
		(
			-- step 1: retrieve premium records for this quote
			SELECT 
				a.QuoteID
				,c.CompanyID
				,SUM(e.Premium) AS Premium -- calculate total premium
				,c.PolicyFee AS PolicyFee
				,c.FilingFee * 
					(	
						SELECT 
							SUM(CASE WHEN c1.Filing = 1 THEN 1 ELSE 0 END)
						FROM 
							Quotes a1 INNER JOIN QuotePersons b1 ON a1.QuoteID = b1.QuoteID
							INNER JOIN QuoteLicenses c1 ON b1.QuotePersonID = c1.QuotePersonID
						WHERE 
							a1.QuoteID = a.QuoteID
					)	
				AS FilingFee
				,dbo.GetMembershipPremium(@QuoteID) as MembershipFee
				,c.StateFeeFactor
				,c.Installments
				,c.InstallmentFee
				,c.PaymentPlanID
				,c.PaymentPlanCode
				,f.Description AS PaymentPlanDisplay
				,c.EFT
				,(c.DownPayPercent/100) AS DownPayPercent
				,CASE 
					WHEN (a.Term = '6m' AND c.Term = '12m') THEN '6 month policy not available, displayed results are for a 12 month policy'
					WHEN (a.Term = '12m' AND c.Term = '6m') THEN '12 month policy not available, displayed results are for a 6 month policy'
					ELSE null
				END AS PremiumMessage
				,SUM(Liab.Premium) AS Liability --calculate liability
				,SUM(Phys.Premium) AS Physical -- calculate physical damage
				,App.CompanyID AS SelectedCompany -- only returns value if company has been previously selected for application
			FROM 
				Quotes a INNER JOIN QuotePaymentPlans b ON a.QuoteID = b.QuoteID AND b.Selected = 1
				INNER JOIN PersAutoPaymentPlans c ON b.CompanyID = c.CompanyID AND b.PaymentPlanID = c.PaymentPlanID 
				INNER JOIN Companies d ON b.CompanyID = d.CompanyID				
				INNER JOIN QuotePremiums e ON b.QuoteID = e.QuoteID AND b.CompanyID = e.CompanyID AND c.PaymentPlanGroup = e.PaymentPlanGroup
				INNER JOIN PersAutoPaymentPlanCodes f ON c.CompanyID = f.CompanyID AND c.PaymentPlanCode = f.PaymentPlanCode
				LEFT OUTER JOIN QuotePremiums Liab ON (e.QuotePremiumID = Liab.QuotePremiumID AND Liab.Coverage NOT IN ('COMP','COLL')) -- rejoin to QuotePremiums to get Liab subset
				LEFT OUTER JOIN QuotePremiums Phys ON (e.QuotePremiumID = Phys.QuotePremiumID AND Phys.Coverage IN ('COMP','COLL')) -- rejoin to QuotePremiums to get Phys subset
				LEFT OUTER JOIN QuoteApps App ON a.QuoteID = App.QuoteID AND d.CompanyID = App.CompanyID  
			WHERE 
				a.QuoteID = COALESCE(@QuoteID, a.QuoteID)
				AND d.Visible = 1
				AND c.StartDate <= a.EffectiveDate 
				AND c.EndDate >= a.EffectiveDate
			GROUP BY 
				c.CompanyID				
				,c.Term
				,c.Installments
				,c.InstallmentFee
				,c.FilingFee
				,c.StateFeeFactor
				,c.DownPayPercent
				,c.PolicyFee
				,c.PaymentPlanID
				,c.PaymentPlanCode
				,f.Description
				,c.EFT
				,d.LongName
				,a.Term
				,a.QuoteID		
				,App.CompanyID
		) AS Sub1
		FULL OUTER JOIN
		(
			-- step 2: retrieve underwriting messages for this quote
			SELECT
				c.CompanyID
				,MIN(b.Message) AS Message
			FROM 
				Quotes a INNER JOIN QuoteUnderwritingMsg b ON a.QuoteID = b.QuoteID
				INNER JOIN Companies c ON b.CompanyID = c.CompanyID
			WHERE
				a.QuoteID = COALESCE(@QuoteID, a.QuoteID)
				AND b.MessageType IN ('Comparison','Policy')
			GROUP BY
				c.CompanyID
				,c.LongName
		) AS Sub2 ON Sub1.CompanyID = Sub2.CompanyID
	) AS Sub3
) AS Sub4
WHERE 
	CompanyID = COALESCE(@CompanyID, CompanyID)  -- allows the ability to return only one record for a specific company
RETURN
END