Solved: Joining on dates to previous month in SQL - Page 2

DataDave · 06-08-2023 08:24 AM

Hi

I have 2 tables where I need to join on customer and date. I need to show all tasks that are in the month before the invoicedate. The invoicedates are always 1st of the month:

e.g if the InvoiceDate is 2023-06-01, then I need to show all tasks between 2023-05-01 and 2023-05-31, and then logic the same for every month.

SELECT
h.tasks
h.date taskdate,
q.date invoicedate

FROM Activitytable h
left join invoice table q on h.Client = q.Customer

ms4446

Apologies. Please try the below query in BigQuery:

SELECT
  h.date AS taskdate,
  q.date AS invoicedate
FROM 
  `activitytable` h
LEFT JOIN 
  `invoice_table` q
ON 
  h.Client = q.Customer
WHERE 
  h.date >= DATE_SUB(DATE_TRUNC(q.date, MONTH), INTERVAL 1 MONTH)
  AND h.date < DATE_TRUNC(q.date, MONTH)
  AND type = 'Invoice'
  AND q.date IS NOT NULL

This corrected query uses the DATE_TRUNC function to get the first day of the month for the invoice date, and the DATE_SUB function to subtract one month from that date. The WHERE clause then filters for tasks that fall within the previous month. It also checks that the invoice date is not null and that the type is 'Invoice'.

View solution in original post