Joining on dates to previous month in SQL

Google Cloud Data Analytics
2

You can achieve this by joining the two tables on Client and Customer, and then use the TIMESTAMP_TRUNC and INTERVAL functions to filter the task dates that fall within the previous month of the invoice date.

Here’s how you can modify your SQL query:

SELECT h.tasks, h.date AS taskdate, q.date AS invoicedate FROM Activitytable h LEFT JOIN invoice_table q ON h.Client = q.Customer WHERE h.date >= TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH) - INTERVAL 1 MONTH AND h.date < TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH)

This query works as follows:

  1. TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH): This function truncates the q.date to the first day of the month.
  2. TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH) - INTERVAL 1 MONTH: This calculates the first day of the previous month.
  3. h.date >= TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH) - INTERVAL 1 MONTH AND h.date < TIMESTAMP_TRUNC(TIMESTAMP(q.date), MONTH): This condition filters the tasks that fall between the first day of the previous month and the first day of the current month.