need a query for a match 2

Question

need a query for a match 2

elsvieta 396

Hi all,

I have these two tables:

T1

Date Item Qty
2025-07-01 100 10
2025-07-01 101 20
2025-07-01 102 20
2025-07-01 105 30
2025-07-02 100 30
2025-07-03 200 20
2025-07-03 201 25
2025-07-03 202 40
2025-07-04 200 30

and T2

Date Item Qty
2025-07-01 100 20
2025-07-01 101 10
2025-07-02 105 30
2025-07-02 106 30
2025-07-03 205 40
2025-07-03 204 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 0
2025-07-01 20 0 20
2025-07-02 30 30 0
2025-07-02 0 30 -30
2025-07-03 20 20 0
2025-07-03 40 40 0
2025-07-03 25 0 25
2025-07-03 0 20 -20
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 should not play any role.

One tentative solution is below, thanks Viorel, however it does not match the quantities:

;

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)

Thanks,

elsvieta

1 answer

Your answer

Answer 1

This is close as I get:

CREATE TABLE T1(Date date,
                item int,
                Qty int)
CREATE TABLE T2(Date date,
                item int,
                Qty int)
INSERT T1(Date, item, Qty)
   VALUES ('2025-07-01', 100, 10),
          ('2025-07-01', 101, 20),
          ('2025-07-01', 102, 20),
          ('2025-07-01', 105, 30),
          ('2025-07-02', 100, 30),
          ('2025-07-03', 200, 20),
          ('2025-07-03', 201, 25),
          ('2025-07-03', 202, 40),
          ('2025-07-04', 200, 30)
INSERT T2(Date, item, Qty)
   VALUES('2025-07-01', 100, 20),
         ('2025-07-03', 204, 20),
         ('2025-07-01', 101, 10),
         ('2025-07-02', 105, 30),
         ('2025-07-02', 106, 30),
         ('2025-07-03', 205, 40),
         ('2025-07-04', 200, 20)
SELECT * FROM T1 ORDER BY Date, Qty
SELECT * FROM T2 ORDER BY Date, Qty
; with Q1 as(
   select *, row_number() over (partition by [Date] 
                                order by IIF(EXISTS (SELECT * 
                                                     FROM   T2
                                                     WHERE  T2.Date = T1.Date
                                                      AND   T2.Qty  = T1.Qty), 0, 1), T1.Qty) as n
   from T1
), Q2 as (
   select *, row_number() over (partition by [Date] 
                                order by IIF(EXISTS (SELECT * 
                                                     FROM   T1
                                                     WHERE  T1.Date = T2.Date
                                                      AND   T1.Qty  = T2.Qty), 0, 1), T2.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)
go
DROP TABLE T1, T2

I don't get this line:

2025-07-03 0 20 -20

I don't understand how you can get four rows for 2025-07-03, when there are only three rows in T1 and one row in T2. But I might have failed in understanding the business logic.

The difference to Viorel's original solution is that I've added a condition to the numbering so say that rows with a matching quantity in the other table should sort before those without a match.

Share via

need a query for a match 2

1 answer

Your answer