The application that we created has undergone significant changes in its database design, especially in how financial data is stored. We have functions that calculate the total amount of billing based on various scenarios; and this change causes huge performance problems when functions have to be executed many times in a row.
I will include an explanation, a function, and an appropriate schema, and I hope someone sees a much better way to write this function. This is SQL Server 2008.
First, the business foundation: think about a medical procedure. The health care provider performing the procedure sends one or more bills, each of which may have one or more items (BillItems).
This procedure is re-billed to the other side. The amount billed to a third party may be:
- Supplier Billing Total
- Provider billing amount plus Copay or
- Fully separate amount (recharge amount)
The current function for calculating billing for the procedure looks in all three scenarios:
CREATE FUNCTION [dbo].[fnProcTotalBilled] (@PROCEDUREID INT)
RETURNS MONEY AS
BEGIN
DECLARE @billed MONEY
SELECT @billed = (SELECT COALESCE((SELECT COALESCE(sum(bi.Amount),0)
FROM BillItems bi INNER JOIN Bills b ON b.BillID=bi.BillID
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.HasCopay=0
AND b.Rebill=0),0))
+
(SELECT COALESCE((SELECT sum(bi.Amount) + COALESCE(b.CopayAmt,0)
FROM BillItems bi INNER JOIN Bills b ON b.BillID=bi.BillID
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.HasCopay=1
GROUP BY b.billid,b.CopayAmt),0))
+
(SELECT COALESCE((SELECT sum(COALESCE(b.RebillAmt,0))
FROM Bills b
INNER JOIN Procedures p on p.ProcedureID=b.ProcedureID
WHERE b.ProcedureID=@PROCEDUREID
AND p.StatusID=3
AND b.Rebill=1),0))
RETURN @billed
END
I will skip DDL for the procedure. It is enough to say that it must have a certain status (shown in the function as p.StatusID = 3).
Here is the DDL for bills and related BillItems:
CREATE TABLE dbo.Bills (
BillID int IDENTITY(1,1) NOT NULL,
InvoiceID int DEFAULT ((0)),
CaseID int NOT NULL,
ProcedureID int NOT NULL,
TherapyGroupID int DEFAULT ((0)) NOT NULL,
ProviderID int NOT NULL,
Description varchar(1000),
ServiceDescription varchar(255),
BillReferenceNumber varchar(100),
TreatmentDate datetime,
DateBilled datetime,
DateBillReceived datetime,
DateBillApproved datetime,
HasCopay bit DEFAULT ((0)) NOT NULL,
CopayAmt money,
Rebill bit DEFAULT ((0)) NOT NULL,
RebillAmt money,
IncludeInDemand bit DEFAULT ((1)) NOT NULL,
CreateDate datetime DEFAULT (getdate()) NOT NULL,
CreatedByID int,
ChangeDate datetime,
ChangeUserID int,
PRIMARY KEY (BillID)
);
CREATE TABLE dbo.BillItems (
BillItemID int IDENTITY(1,1) NOT NULL,
BillID int NOT NULL,
ItemDescription varchar(1000),
Amount money,
WillNotBePaid bit DEFAULT ((0)) NOT NULL,
CreateDate datetime DEFAULT (getdate()),
CreatedByID int,
ChangeDate datetime,
ChangeUserID varchar(25),
PRIMARY KEY (BillItemID)
);
I fully understand how complex the function is; but I could not find another way to account for all the scenarios.
I hope that a much better SQL programmer or database administrator will see a more efficient solution.
Any help would be greatly appreciated.
Thank,
Tom
UPDATE:
. , .
-, : - . ; .
"" .
, - . -. Copay ( ) BillItems. .
Case ( ) , .
, .
, @Serpiton SQL Fiddle - , . .
, CTE @Serpiton @GarethD - . CTE, SELECT.
@Serpiton CTE Case. , , , . , .
:
WITH Normal As (
SELECT b.BillID
, b.CaseID
, sum(coalesce(n.Amount * (1 - b.Rebill), 0)) Amount
FROM Procedures p
INNER JOIN Bills b ON p.ProcedureID = b.ProcedureID
LEFT JOIN BillItems n ON b.BillID = n.BillID
WHERE b.CaseID = 3444
AND p.StatusID = 3
GROUP BY b.CaseID,b.BillID, b.HasCopay
)
SELECT Amount = Sum(b.Amount)
+ Sum(Coalesce(c.CopayAmt, 0))
+ Sum(Coalesce(r.RebillAmt, 0))
FROM Normal b
LEFT JOIN Bills c ON b.BillID = c.BillID And c.HasCopay = 1
LEFT JOIN Bills r ON b.BillID = r.BillID And r.Rebill = 1
GROUP BY b.caseid