CODEWITHSUNDEEP
mysqlsql
Corie Slate
Corie Slate

Reputation: 616

Select latest date from 2 tables and between specified dates

I have a query in which I am getting the SUMs of records that fall within a certain date range. Now I am trying to include the latest date of one column within each date range.

SQL Fiddle:

http://sqlfiddle.com/#!9/2b486/6

The Query:

SELECT
Contract.contract_length,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-01-01 00:00:00' AND '2013-03-31 23:59:59'
  THEN 1 ELSE 0 END
) as rental_count_Q1_2013,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-01-01 00:00:00' AND '2013-03-31 23:59:59'
    AND termination_date BETWEEN '2013-01-01 00:00:00' AND NOW()
  THEN 1 ELSE 0 END
) as terminated_count_Q1_2013,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-01-01 00:00:00' AND '2013-03-31 23:59:59'
    AND(
      Data1Payout.payout_date BETWEEN '2013-01-01 00:00:00' AND NOW()
      OR Data2Payout.payout_date BETWEEN '2013-01-01 00:00:00' AND NOW()
    )
  THEN 1 ELSE 0 END
) as customer_payouts_Q1_2013,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-04-01 00:00:00' AND '2013-06-30 23:59:59'
  THEN 1 ELSE 0 END
) as rental_count_Q2_2013,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-04-01 00:00:00' AND '2013-06-30 23:59:59'
    AND termination_date BETWEEN '2013-04-01 00:00:00' AND NOW()
  THEN 1 ELSE 0 END
) as terminated_count_Q2_2013,
SUM(
  CASE WHEN
    Contract.created BETWEEN '2013-04-01 00:00:00' AND '2013-06-30 23:59:59'
    AND(
      Data1Payout.payout_date BETWEEN '2013-04-01 00:00:00' AND NOW()
      OR Data2Payout.payout_date BETWEEN '2013-04-01 00:00:00' AND NOW()
    )
  THEN 1 ELSE 0 END
) as customer_payouts_Q2_2013

FROM contracts AS Contract
LEFT OUTER JOIN (
  SELECT id, customer_id, payments_made, termination_date FROM terminations
) AS Termination on Termination.customer_id = Contract.customer_id
LEFT OUTER JOIN (
  SELECT id, customer_id, payout_date FROM datasource_1_payouts
) AS Data1Payout on Data1Payout.customer_id = Contract.customer_id
LEFT OUTER JOIN (
  SELECT id, customer_id, payout_date FROM datasource_2_payouts
) AS Data2Payout on Data2Payout.customer_id = Contract.customer_id
GROUP BY Contract.contract_length

This returns a count of rentals, terminations of those rentals, and payouts of those rentals for each quarter and grouped by the length of contract.

contract_length | rental_count_Q1_2013 | terminated_count_Q1_2013 | customer_payouts_Q1_2013 | rental_count_Q2_2013 | terminated_count_Q2_2013 | customer_payouts_Q2_2013
24              |           2          |           1              |           1              |           1          |           0              |           1
36              |           2          |           1              |           1              |           2          |           0              |           2
48              |           1          |           1              |           0              |           2          |           1              |           1

I need to also get the last payout date for each quarter. I.E. the date last payout was made from the contracts that were created in 1st quarter of 2013. The payouts are also stored in 2 separate tables.

What I have tried:

Using MAX():

CASE WHEN
    Contract.created BETWEEN '2013-01-01 00:00:00' AND '2013-03-31 23:59:59'
    AND Data1Payout.payout_date BETWEEN '2013-04-01 00:00:00' AND NOW()
    AND MAX(Data1Payout.payout_date) > MAX(Data2Payout.payout_date)
    THEN MAX(Data1Payout.payout_date) ELSE MAX(Data2Payout.payout_date) END
as last_payout_date_Q1_2013

This doesn't work because all the payout dates are included in each result so the MAX() is always the latest overall.

Working Solution:

MAX(
    CASE WHEN Contract.created BETWEEN '2013-01-01 00:00:00' AND '2013-03-31 23:59:59'
    THEN GREATEST(IFNULL(Data1Payout.payout_date, '0000-00-00'), IFNULL(Data2Payout.payout_date, '0000-00-00'))
    ELSE NULL END
) AS last_payout_date_Q1_2013

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

MySQL offers a nice way to simplify your existing query. For instance, the first value could be calculated as:

SELECT Contract.contract_length,
       SUM(Contract.created >= '2013-01-01' AND contract.created < '2013-04-01'
          ) as rental_count_Q1_2013,

Your question is unclear as to which column you want to maximum. But, you can use the same idea:

SELECT Contract.contract_length,
       MAX(CASE WHEN Contract.created >= '2013-01-01' AND contract.created < '2013-04-01'
                THEN Contract.created
          ) as rental_count_Q1_DATE,

Notice that I changed the date logic to just use days (with a strict < on the second date). I think this is safer (and easier to read) then between. This logic works regardless of whether or not the column has a time component. And, it works for all seconds of the day.

Also, you should not use subqueries in the FROM clause, unless you need to, and you do not need to. There is the overhead of materializing them (only in MySQL). More importantly, they prevent the use of indexes for optimizing the query.

Upvotes: 1

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

You put MAX function at the wrong place. Actually, it's the same as other CASE Statements.
Since your query was too long, I will give a simple explanation, and I hope this will help you out.

SELECT 
    SUM(
    CASE WHEN x = 'yes'
    THEN 1 ELSE 0
    END) AS SUM_NUMBER, --terminated_count_Q1_2013 for example
    MAX(
    CASE WHEN x = 'yes'
    THEN yourdatecolumn
    ELSE NULL END) AS MAX_DATE --last_payout_date_Q1_2013 for example
FROM yourtable

I hope you know how to implement this on your own query.

Upvotes: 1

Related Questions