Joining on dates to previous month in SQL

Transact-SQL
1

Hi

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.

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

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

2 

SELECT
    ...
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)
3

Thanks. Do you how this would work in BigQuery ?

4

Sorry, I have no idea (this is a SQL Server forum, so that's the primary focus here).

5

No worries. Thanks. I am getting close.

6

BigQuery and SQL server are very similar

7

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.

8

yeh, I agree. I am looking into that, Thanks for your help @ScottPletcher

9

According to Google, something like:


SELECT DATE_ADD(DATE <date>, INTERVAL n MONTH)
10

here is the answer in case anyone want to know :slight_smile:

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

1 Like