Joining on dates to previous month in SQL

[DataDave]

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]

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.

[DataDave]

Thank you, using this script below, I get the following error:

Query error: No matching signature for operator >= for argument types: DATE, TIMESTAMP. Supported signature: ANY >= ANY at [11:7]

SELECT

h.date taskdate,

q.date 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)

and type = 'Invoice'

and q.date is not null

[ms4446 · Accepted solution]

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'.

[DataDave]

Amazing!! I think that worked. Thank you so much