need a query to do a match

Question

need a query to do a match

elsvieta 416

Hi all,

I have these two tables:

T1

Date Item Qty
2025-07-01 100 10
2025-07-01 101 20
2025-07-02 100 30
2025-07-03 200 20
2025-07-04 200 30

and T2

Date Item Qty
2025-07-01 100 10
2025-07-02 105 30
2025-07-02 106 30
2025-07-03 205 20
2025-07-04 200 20

I need a query that gives me a result exactly like below:

Result Table
Date T1Qty T2Qty Diff(T1Qty-T2Qty)
2025-07-01 10 10 0
2025-07-01 20 20
2025-07-02 30 30 0
2025-07-02 0 30 -30
2025-07-03 20 20 0
2025-07-04 30 20 10

On aggregate, the Qty difference for 2025-07-01 is 20, whether I match my second row in T2 with the first row in T1 and I get a 10 difference for that row and then the first row in T1 is not matched with anything in T2 and there is another difference of 10.

However, I would like to match the quantities, as well, so that my result is like shown in the result table. The Item does not play any role.

Thanks,
elsvieta

Accepted answer

0 additional answers

Your answer

Answer 1

Viorel 123.6K

Try something like this:

;
with Q1 as 
(
	select *, row_number() over (partition by [Date] order by Qty) as n
	from T1
),
Q2 as 
(
	select *, row_number() over (partition by [Date] order by Qty) as n
	from T2
)
select coalesce(Q1.[Date], Q2.[Date]) as [Date], coalesce(Q1.Qty, 0) as T1Qty, coalesce(Q2.Qty, 0) as T2Qty, coalesce(Q1.Qty, 0) - coalesce(Q2.Qty, 0) as [Diff(T1Qty-T2Qty)]
from Q1
full outer join Q2 on Q1.[Date] = Q2.[Date] and Q1.n = Q2.n
order by [Date], coalesce(Q1.Qty, Q2.Qty)

elsvieta 416 Reputation points

2025-07-09T18:34:08.1833333+00:00

Hi Viorel,

I guess we are close to the wanted solution. It worked well with my simplified model, however when I applied your query to my real model I got a slightly different result.

I will accept you answer as a solution, but please keep an eye on my posts, I will probably need more help with this.

Thank you very much,

elsvieta
elsvieta 416 Reputation points

2025-07-11T17:38:19.23+00:00

Hi Viorel,

I can see now what the problem is with my real data; it was not reflected in my simple sample.

I have the situation where row_number() 1 let's say has Qty 100 and row_number() 2 has Qty 200 in T1 and row_number() 1 in T2 has Qty 200. I believe that the query above will write three rows in the result as no match is encountered. The idea is that anywhere I have matching quantities they should written on one row with a difference of 0, regardless of their row_number() in their table. I will try to come up with another sample that reflects this rule.

Thank you in advance,

elsvieta
elsvieta 416 Reputation points

2025-07-11T17:43:53.7433333+00:00

Viorel,

I will open a new question, just so you know.

Thanks,

elsvieta

Share via

need a query to do a match

0 additional answers

Your answer