Trouble using Max() function

Question

I have 2 tables: Customer and Billing, Customer contains generic details (name, address, you get the idea) and Billing contains items that are billed.

The end date of when the item gets billed to is variable (some people are on a monthly cycle, others on a 3 months, 6 months 1 year etc.).

In the Billing Item the customer can have a number of packages - some of them are packages that mark them as a Reseller. My current SQL query looks like this:

select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       max(billing.end_date) 
from customer 
INNER JOIN billing on customer._id = billing.customer_$id 
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email, 
         billing.lines_0_description 

Which outputs the following:

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        Test@test.com   true        2012-01-23

Which is a list of all the user data (that we want) but joined to each billing line, as opposed to selecting the end_date with the largest value.

I suspect I've got to do a nested select, but I'm not sure on the logic that I need here.

Suggestions?

Answer 1

You cannot get a single MAX value for end_date while you're grouping on lines_0_description because each billing record will, presumably, have a different description producing a different distinct group and different max end_date values.

You can use a CTE to extract the most recent billing record per customer then join that to your main SELECT to produce the result set you want. Something like below should work (disclaimer: I've guessed at some column names, e.g. _id in the billing table, as I don't know the schema)

;WITH BillingRecords AS (
  SELECT MAX(_id) AS billing_id,
    customer_$id
  FROM billing
  WHERE (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%')
  GROUP BY customer_$id
)


  select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       billing.end_date
from customer 
INNER JOIN BillingRecords br ON br.customer_$id = customer._id
INNER JOIN billing on billing._id = br.billing_id
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email

Answer 2

You could use a window function for this. Using a common table expression to provide a ranking order of the dates and then filtering on the order in the final select statement.

with Cte as (
    select ...,
           ROW_NUMBER() OVER (PARTITION BY billing.customer_$id ORDER BY billing.end_date DESC) AS Date_Rank
    From ...
)
select ...
from Cte
where Date_Rank = 1

https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

Apologies for the lack of formatting, this is my first post and I'm in a bit of a hurry.

Answer 3

Generally I prefer the CTE method posted by JHackney, but if you are trying to avoid a rewrite (for now), just change:

group by customer.addresses_0_firstname, 
     ...,
     billing.lines_0_description 

To

group by customer.addresses_0_firstname, 
     ...,
     (CASE WHEN billing.lines_0_description like '%Package0%' 
         THEN 'False' 
         ELSE 'true' END)

The problem is you want to collapse all these different values into two distinct strings, but if you group by the original string instead of the collapsed output, you'll have multiple rows contributing groups but that won't be obvious in the output. (You will see what I mean if you just output that lines_0_description column instead of the CASE expression in your existing query.)