Get results from two time intervals

Question

There is a query which brings back sales data for the last 7 days. How to get the sales of the last 30 days as well (to see the sales for the last 7 days AND the last 30 days in the results)?

SELECT 
  a.row_id,
  MAX(ad.new_value) - MIN(ad.new_value) AS sales7days 
FROM
  _audit a 
  LEFT JOIN _audit_data ad 
    ON a.audit_id = ad.audit_id 
WHERE ad.col = 'sales' 
  AND a.triggered_datetime > NOW() - INTERVAL 7 DAY 
GROUP BY a.row_id 
ORDER BY sales7days DESC;

Answer 1

Perhaps with a CASE expression:

SELECT a.row_id
   ,  MAX(case when a.triggered_datetime > NOW() - INTERVAL 7 DAY 
               then ad.new_value else NULL end)
    - MIN(case when a.triggered_datetime > NOW() - INTERVAL 7 DAY
               then ad.new_value else NULL end) AS sales7days 
   ,  MAX(case when a.triggered_datetime > NOW() - INTERVAL 30 DAY 
               then ad.new_value else NULL end)
    - MIN(case when a.triggered_datetime > NOW() - INTERVAL 30 DAY
               then ad.new_value else NULL end) AS sales30days 
FROM  _audit a, _audit_data ad 
WHERE a.audit_id = ad.audit_id AND ad.col = 'sales' 
GROUP BY a.row_id;

Answer 2

SELECT 
  d7.row_id,
  d7.salesdays, d30.salesdays
FROM
(
  Select a.row_id, MAX(ad.new_value) - MIN(ad.new_value) AS salesdays
  From _audit a 
  LEFT JOIN _audit_data ad ON a.audit_id = ad.audit_id 
WHERE ad.col = 'sales' AND a.triggered_datetime > NOW() - INTERVAL 7 DAY 
GROUP BY a.row_id 
) d7,
(
  Select a.row_id, MAX(ad.new_value) - MIN(ad.new_value) AS salesdays
  From       _audit a 
  LEFT JOIN _audit_data ad ON a.audit_id = ad.audit_id 
WHERE ad.col = 'sales' AND a.triggered_datetime > NOW() - INTERVAL 30 DAY 
GROUP BY a.row_id 
) d30

where d7.row_id = d30.row_id
ORDER BY sales7days DESC;

assume you want the same row id for both - and either value to show, you may or may not want to make it inner or outer joined and/or COALESCE the value fields (don't know enough about the data).