Creating an Update Report using Joins in SQL

Question

I have Sales data in Microsoft Access 2016 that I am trying to update on a weekly basis to show Customer ID, Most Recent Purchase, Order Qty, and Return Qty. As the weeks go by these values can change for any customer so on the Updated Report should show the newest data. Example:

TableWeek1
| Customer ID | Purchase Date | Order Qty | Return Qty |
| ----------- | ------------- | --------- | ---------- |
|   1234567   |  01/02/2023   |      1    |      0     |
|   2468135   |  01/03/2023   |      3    |      0     |
|   1357924   |  01/03/2023   |      2    |      0     |
|   3579512   |  01/04/2023   |      2    |      1     |

TableWeek2
| Customer ID | Purchase Date | Order Qty | Return Qty |
| ----------- | ------------- | --------- | ---------- |
|   1234567   |  01/09/2023   |      0    |      1     |
|   2468135   |  01/10/2023   |      0    |      2     |
|   1357924   |  01/11/2023   |      3    |      0     |
|   1597538   |  01/13/2023   |      0    |      2     |

I want the result to show:

UpdateReport
| Customer ID | Purchase Date | Order Qty | Return Qty | Net Purchase Qty |
| ----------- | ------------- | --------- | ---------- | ---------------- |
|   1234567   |  01/09/2023   |      1    |      1     |         0        |
|   2468135   |  01/10/2023   |      3    |      2     |         1        |
|   1357924   |  01/03/2023   |      5    |      0     |         5        |
|   3579512   |  01/04/2023   |      2    |      1     |         1        |
|   1597538   |  01/13/2023   |      0    |      2     |         -2       |

So in the Update Report I have all customers, the latest purchase date, the sum of the order qty, the sum of the return qty, and the difference between the total order and the total return.

I tried

SELECT [Customer ID], [Purchase Date], [Order Qty], [Return Qty]
FROM TableWeek1 
FULL JOIN TableWeek2 ON 
TableWeek1.[Customer ID]=TableWeek2.[Customer ID]

and was playing with cases to get the results I wanted but they weren't working. What I want is in the Update Report I have all customers, the latest purchase date, the sum of the order qty, the sum of the return qty, and the difference between the total order and the total return.

Answer 1

Setting up your example data:

create table #TableWeek1
(
    [Customer ID] int,
    [Purchase Date] date,
    [Order Qty] int,
    [Return Qty] int
)

create table #TableWeek2
(
    [Customer ID] int,
    [Purchase Date] date,
    [Order Qty] int,
    [Return Qty] int
)

insert into #TableWeek1 values
(1234567,'01/02/2023',1,0),
(2468135,'01/03/2023',3,0),
(1357924,'01/03/2023',2,0),
(3579512,'01/04/2023',2,1)

insert into #TableWeek2 values
(1234567,'01/09/2023',0 ,1),
(2468135,'01/10/2023',0 ,2),
(1357924,'01/11/2023',3 ,0),
(1597538,'01/13/2023',0 ,2)

This query will give your desired output:

select
    coalesce(w1.[Customer ID],w2.[Customer ID]) as [Customer ID],
    max(case when isnull(w2.[Purchase Date],'1900-01-01') > isnull(w1.[Purchase Date],'1900-01-01') then w2.[Purchase Date] else w1.[Purchase Date] end) as [Purchase Date],
    sum(isnull(w1.[Order Qty],0)) + sum(isnull(w2.[Order Qty],0)) as [Order Qty],
    sum(isnull(w1.[Return Qty],0)) + sum(isnull(w2.[Return Qty],0)) as [Return Qty],
    (sum(isnull(w1.[Order Qty],0)) + sum(isnull(w2.[Order Qty],0))) - (sum(isnull(w1.[Return Qty],0)) + sum(isnull(w2.[Return Qty],0))) as [Net Purchase Qty]
from 
(
    select [Customer ID] from #TableWeek1
    union
    select [Customer ID] from #TableWeek2
) c
left join #TableWeek1 w1 on c.[Customer ID] = w1.[Customer ID]
left join #TableWeek2 w2 on c.[Customer ID] = w2.[Customer ID]
group by coalesce(w1.[Customer ID],w2.[Customer ID])

This assumes that not every Customer ID will always appears in both tables, but could be simplified if you know that they in fact would.

Results:

Customer ID	Purchase Date	Order Qty	Return Qty	Net Purchase Qty
1234567	09/01/2023	1	1	0
1357924	11/01/2023	5	0	5
1597538	13/01/2023	0	2	-2
2468135	10/01/2023	3	2	1
3579512	04/01/2023	2	1	1