Sql-server – Trouble using Max() function

sql serversql server 2014

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?

Best Answer

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
Related Question