Get sums of vacancies on two different fields-CodePudding

I have a table "vacancies", which consists of an id, vacancy_status_id, published_date, created and updated.

The created indicates the time the row was created in the database, the updated indicates last time any value in the row changed and published_date indicates the time the vacancy went to status 2 (active).

Apart from that I have created a calendar table that contains all dates from 2010-01-01 to 2050-12-31 (as described here: https://ubiq.co/database-blog/fill-missing-dates-in-mysql/ )

I want to get the amount of active vacancies and the amount of inactive vacancies per day starting from the date the first vacancy was created in my database.

The tricky part is, that the active vacancies are calculated based on the "published_date" field, while the inactive vacancies are based on the "updated" field.

I am able to write this query as 2 separate queries, but I'm not able to combine these into one query:

SELECT c.datefield AS created, 
  SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield

SELECT c.datefield AS created, 
  SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.updated)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield

How could I combine these two in one query ?

My result should show :

created | amount_active_vacancies | amount_inactive_vacancies

CodePudding user response：

If your two queries are correct, you can join them together

SELECT a.created, a.amount_active_vacancies, b.amount_inactive_vacancies
FROM (
    SELECT c.datefield AS created, 
      SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies
    FROM calendar AS c
    LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date)
    WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
    GROUP BY c.datefield
) a 
JOIN (
    SELECT c.datefield AS created, 
      SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
    FROM calendar AS c
    LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.updated)
    WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
    GROUP BY c.datefield
) b
WHERE a.created = b.created

or combine the conditions

SELECT c.datefield AS created, 
  SUM(case when v.vacancy_status_id=2 then 1 else 0 end) as amount_active_vacancies,
  SUM(case when v.vacancy_status_id=3 OR v.vacancy_status_id=4 then 1 else 0 end) as amount_inactive_vacancies
FROM calendar AS c
LEFT OUTER JOIN vacancy AS v ON c.datefield = DATE(v.published_date) OR c.datefield = DATE(v.updated)
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM vacancy) AND DATE(NOW() - INTERVAL 1 DAY)
GROUP BY c.datefield