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?
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 singlebilling.lines_0_description LIKE '%package[1-3]%'
operator.