title: SQL Server grouping foiled by case statement
body: <p>My applications has Bills, which in turn may have multiple line items (billItems).</p>
  
  <p>A Bill may also have a Copay amount, which is added to the total of the line items.</p>
  
  <p>It may also have a Rebill amount, which supercedes the line items altogether.</p>
  
  <p>I have a function which needs to return a total billing amount. Currently, it looks like this:</p>
  
  <pre><code>SELECT @billed = (SELECT CASE WHEN b.Rebill=1 THEN ISNULL(b.RebillAmt,0)
          WHEN b.HasCopay=1 THEN ISNULL(SUM(bi.Amount),0) + ISNULL(b.CopayAmt,0)
          ELSE ISNULL(SUM(bi.Amount),0) END AS TotalBilled
      FROM BillItems bi 
          INNER JOIN Bills b ON b.BillID=bi.BillID
          INNER JOIN Procedures p ON p.ProcedureID=b.ProcedureID
      WHERE p.CaseID = @CASEID
      AND     p.StatusID=3
      GROUP BY b.CaseID,b.Rebill,b.RebillAmt,b.HasCopay,b.CopayAmt)
  RETURN @billed
  </code></pre>
  
  <p>When there are no copays or rebill amounts, the function returns a single total. However, if there are copay or rebill amounts, the inner SELECT returns a record for each Bill which has either a copay or rebill, and a record with a total for those Bills which have neither a copay or rebill amount.</p>
  
  <p>How should I restate this so that I get a single total billing amount for each "Case"?</p>
  
  <p>(This is a SQL Server 2008 installation.)</p>
  
  <p>Thanks,</p>
  
  <p>Tom</p>
  
  <p><strong>Here is further information: first, the business purpose and rules.</strong></p>
  
  <p>Think of a course of medical treatment. For reference, call it a "Case."</p>
  
  <p>Each case will have a series of treatments (Procedures). For a given Procedure, one or more Bills will be received. For each of those Bills, there may be one or more billing line items (BillItems), each of which will have an Amount.</p>
  
  <p>For each Bill, the total final billing amount will be either:</p>
  
  <ul>
  <li>The total of the Amounts of all related BillItems, or</li>
  <li>if the Bill.HasCopay flag is true, and there is a CopayAmt, the total of all related BillItem Amounts plus the CopayAmt; or</li>
  <li>if the Bill.Rebill flag is true and there is a RebillAmt, the RebillAmt</li>
  </ul>
  
  <p>So if the Bill is to be "rebilled," the RebillAmt is the total billing. If there is a CopayAmt, it is added to the total of the BillItems. Otherwise, we just total the BillItems.</p>
  
  <p>The goal is to aggregate a single total billing amount for a case, based on applying the above rules to each Bill.</p>
  
  <p>The DDLs are below. The Case table has been omitted, because it has many irrelevant fields. Suffice to say, it is represented by the CaseID relationship in the other tables.</p>
  
  <p>The tables are not normalized, in that there are redundant relationships back to the Case. This was purposely done for ease of reporting. You can certainly disagree with the design, but that's the way it is.</p>
  
  <p>The foreign key relationships are not shown in the code below, but from the rules above, it should be apparent that Cases have Procedures which have Bills which have BillItems.</p>
  
  <p>And finally, for clarity the "p.StatusID=3" in the original statement simply identifies Procedures which have been "approved." All others are ignored.</p>
  
  <p>Thanks very much for the help. Hopefully, this will be enough information to make the question more answerable. Here's the code:</p>
  
  <pre><code>CREATE TABLE Procedures (
  ProcedureID int IDENTITY(1,1) NOT NULL,
  RequestID int NOT NULL,
  StatusID int,
  ProviderLocationID int DEFAULT ((0)) NOT NULL,
  Description varchar(3000),
  CPTcodes varchar(500),
  Rejected bit DEFAULT ((0)) NOT NULL,
  CreateDate datetime DEFAULT (getdate()) NOT NULL,
  CreatedByID int,
  ChangeDate datetime DEFAULT (getdate()) NOT NULL,
  ChangeUserID int,
  ApprovalDate datetime,
  ApprovalCode varchar(25),
  ShowCPTCodes bit DEFAULT ((1)) NOT NULL,
  DispositionDate datetime,
  PreviouslyApproved bit DEFAULT ((0)) NOT NULL,
  SubmissionDate datetime,
  DenialDate datetime,
  IsPT bit DEFAULT ((0)) NOT NULL,
  DenialReason varchar(1000),
  PriorProcedure bit DEFAULT ((0)) NOT NULL,
  UseCommonTreatmentDates bit DEFAULT ((1)) NOT NULL,
  IsComplete bit DEFAULT ((0)) NOT NULL,
  CommonTreatmentDate datetime,
  TreatmentNoteReceived varchar(25) DEFAULT ('No') NOT NULL,
  TreatmentNoteRead bit DEFAULT ((0)) NOT NULL,
  TreatmentNoteReceivedDate datetime,
  TreatmentNoteReadDate datetime,
  InvoiceReceived varchar(25),
  InvoiceReceivedDate datetime,
  CaseID int DEFAULT ((0)) NOT NULL,
  TreatmentDate datetime,
  ScheduledDate datetime,
  PTRemoved bit DEFAULT ((0)) NOT NULL,
  ProviderID int DEFAULT ((0)) NOT NULL,
  TherapyGroupID int DEFAULT ((0)) NOT NULL,
  SendDemographics bit DEFAULT ((0)) NOT NULL,
  SendOrder bit DEFAULT ((0)) NOT NULL,
  TreatmentNoteNotNeeded bit DEFAULT ((0)) NOT NULL,
  PRIMARY KEY (ProcedureID)
  </code></pre>
  
  <p>);</p>
  
  <pre><code>CREATE TABLE 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)
  </code></pre>
  
  <p>);</p>
  
  <pre><code>CREATE TABLE 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)
  </code></pre>
  
  <p>);</p>