0

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?

  • 1) If I understand you correctly you want to obtain only one output record with maximal end_date instead of two records shown, is it? If so the result you need is MAX(MAX()) - and it needs 2 GROUP BY levels. Move your query to the WITH section and obtain the data you need in the main query. 2) Replace 3 LIKEs linked via ORs with single billing.lines_0_description LIKE '%package[1-3]%' operator. – Akina Sep 19 '18 at 5:17
  • That's right - on the live DB - the total results is something like 30+ - I've just added 2 for reference - I'll try your suggestion, as for the 3 likes - in the Live DB, the names are different (which translate to actual plans that are published on a website - hence I obscured them with a generic Package1, package2 etc.) – TheDemonLord Sep 19 '18 at 5:24
2

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
  • I had to add some 2 additional group bys: billing.lines_0_description, billing.end_date But I've got the output I expected - thank you for your help - I've not used the 'WITH' function before, so will have to look up the logic on that :) – TheDemonLord Sep 19 '18 at 20:31
0

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.

  • Welcome to DBA.SE. We appreciate your contribution. However, the community does expect a certain quality in the answers posted. Please consider reading the following article: How do I write a good answer? (Help Centre) In the section Answer the question there is a short sentence which reads: Brevity is acceptable, but fuller explanations are better. You might want to edit your answer and add a working example. – John aka hot2use Sep 19 '18 at 11:13
0

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.