I have 2 tables where I need to join on customer and date. I need to show all tasks that fall in the month before the invoicedate. The invoicedates are always 1st of the month:
e.g if the InvoiceDate is 2023-06-01 I need to show all tasks between 2023-05-01 and 2023-05-30, and then the same for every month.
FROM Activitytable h
left join invoice table q on h.Client = q.Customer
FROM invoicetable i
LEFT OUTER JOIN Activitytable a ON a.Client = i.Customer AND
a.date >= DATEADD(MONTH, -1, i.date) AND
a.date < DATEADD(MONTH, 1, i.date)
Thanks. Do you how this would work in BigQuery ?
Sorry, I have no idea (this is a SQL Server forum, so that's the primary focus here).
No worries. Thanks. I am getting close.
BigQuery and SQL server are very similar
As I understand it, BigQuery supports standard SQL. So the issue would be on how to adjust the date values in BigQuery, and that I don't know.
yeh, I agree. I am looking into that, Thanks for your help @ScottPletcher
According to Google, something like:
SELECT DATE_ADD(DATE <date>, INTERVAL n MONTH)
here is the answer in case anyone want to know
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