Group By clause over a series of data SQL

Question

I am in the process of writing a report and am having some trouble getting a series of data to return correctly (Data warehouse may be ideal, but not an option right now). Effectively, I have 2 tables I need to join and report on...

Transactions and Receipts. Transactions contain the amount billed and Receipts contain the amount paid. My report needs to show:

LastName | FirstName | Company | Location | Total | 30 | 60 | 90

---------------------------------------------------------------------
Tom      |  Clark | Microsoft | Washington | $300 | $80 | $100 | $120  

Where 30,60,90 are buckets to show the amount owed 30 days ago, 60 days ago, etc. This is where I'm struggling. I can get the other values without issue. This is what I have thus far:

select 
    st.Client_Name_Last,
    st.Client_Name_First,
    st.Location_Company,
    st.Location_Address_City,
    sum((st.Billing_AmountPerUnitAllowed * st.Billing_NumberOfUnits) - coalesce(r.PaymentAmount, 0)) as Total,
    (select sum((st.Billing_AmountPerUnitAllowed * st.Billing_NumberOfUnits) - coalesce(r.PaymentAmount, 0)) 
        where DateDiff(day, st.service_date,  @effectiveDate) > 0 and DateDiff(day, st.service_date,  @effectiveDate) < 30) as '30',
    (select sum((st.Billing_AmountPerUnitAllowed * st.Billing_NumberOfUnits) - coalesce(r.PaymentAmount, 0))
        where DateDiff(day, st.service_date,  @effectiveDate) >= 30 and DateDiff(day, st.service_date,  @effectiveDate) < 60) as '60'

 from 
    ServiceTransactions st
    join Claims c on st.Claim_Id = c.Id
    left outer join Receipts r on c.Id = r.ClaimId

group by 
    st.Client_Name_Last,    
    st.Client_Name_First,   
    st.Location_Company,
    st.Location_Address_City

This of course doesn't work because the st.Service_Date is in the top level select statement, which causes an error because it's not in an aggregate or the group by clause. I've considered going with a Common Table Expression, but wasn't sure how to best utilize that. Any insight would be most appreciated.

Thanks for your time!

Answer 1

You want conditional aggregation. This puts the case inside the sum():

sum(case when DateDiff(day, st.service_date,  @effectiveDate) > 0 and DateDiff(day, st.service_date,  @effectiveDate) < 30)
         then (st.Billing_AmountPerUnitAllowed * st.Billing_NumberOfUnits) - 
              coalesce(r.PaymentAmount, 0)
          else 0
     end) as days_30,
sum(case when DateDiff(day, st.service_date,  @effectiveDate) >= 30 and DateDiff(day, st.service_date,  @effectiveDate) < 60)
         then (st.Billing_AmountPerUnitAllowed * st.Billing_NumberOfUnits) - 
              coalesce(r.PaymentAmount, 0)
          else 0
     end) as days_60