Match by a date

Transact-SQL
1

Hello, I have two tables (Supplied and Orders) that are related by a serial number and date. The dates do not match they are just close. I came up with a rank to find matching data, but it does not work if there are more Supplied records than Orders for a given serial number. To demonstrate I have:

CREATE TABLE #SuppliedData (SerialNum VARCHAR(50), TDate DATE);
INSERT INTO #SuppliedData (SerialNum, TDate) VALUES
('353223104176111','2021-03-11'),
('353223104176111','2021-07-15'),
('357379091537024','2021-03-03'),
('357379091537024','2021-06-04'),
('358230100878942','2021-07-07');

CREATE TABLE #OrderData (SKU VARCHAR(50), OrdDate DATETIME, OrdNum INT);
INSERT INTO #OrderData (SKU, OrdDate, OrdNum) VALUES
('353223104176111','2021-03-17 15:50:40.453', 163705600),
('353223104176111','2021-07-20 15:13:54.057', 172160150),
('357379091537024','2021-06-15 11:47:52.117', 169843363),
('358230100878942','2021-07-07 19:25:46.980', 171359335),
('358230100878942','2021-07-08 13:01:59.863', 171399027);

CREATE TABLE #CurrentOutput (SerialNum VARCHAR(50), ODate DATE, OrdNum INT);
INSERT INTO #CurrentOutput (SerialNum, ODate, OrdNum) VALUES
('353223104176111','2021-03-11', 163705600),
('353223104176111','2021-07-15', 172160150),
('357379091537024','2021-03-03', 169843363),
('357379091537024','2021-06-04', NULL),
('358230100878942','2021-07-07', 171399027);

CREATE TABLE #WantedOutput (SerialNum VARCHAR(50), ODate DATE, OrdNum INT);
INSERT INTO #WantedOutput (SerialNum, ODate, OrdNum) VALUES
('353223104176111','2021-03-11', 163705600),
('353223104176111','2021-07-15', 172160150),
('357379091537024','2021-03-03', NULL),
('357379091537024','2021-06-04', 169843363),
('358230100878942','2021-07-07', 171399027);

For serial number 357379091537024 there is only one record in the orders table but two records in the supplied table. My logic populates the order number but for the wrong date.
If you can point me to a solution I would be grateful.

Basically I need a way to combine the tables #SuppliedData and #OrderData on serial number and date to get the desired result.

This is my first post for some time as I have had some job interruption. I hope I have the enough info here.

2

hi

sorry to say this

but i tried

your data does not match what you put in current and wanted

please explain in a way that we can understand

it looks really really easy to do

3

Thank you. I was afraid I did not explain correctly.
The way I got to the current was using
ROW_NUMBER () OVER (PARTITION BY SKU ORDER BY OrdDate DESC) RNum
and
ROW_NUMBER () OVER (PARTITION BY SerialNum ORDER BY Tdate DESC) RNum
which I then joined on
Ord.SKU = Sp.SerialNum AND Ord.OrdDate >= Sp.TDate AND Sp.Rnum = Ord.RNum

This worked well except for the problem of finding the correct date as described above.

4

gotcha

now let me try

5

The relative row numbers won't always match up. The tricky part will be not assigning the same Ord row to more than one Supplied row.

I wrote this code before you added your further explanation.

SELECT
    SD.SerialNum, OD.OrdDate, OD.OrdNum
FROM #SuppliedData SD
OUTER APPLY (
    SELECT TOP (1) *
    FROM #OrderData OD
    WHERE 
        OD.SKU = SD.SerialNum AND
        OD.OrdDate >= DATEADD(DAY, 1, SD.TDate) AND
        OD.OrdDate <= DATEADD(DAY, 14, SD.TDate)
) AS OD
ORDER BY SerialNum, OrdDate
6

hi there you go

Scotts answer will help you OUT

i still dont understand WHAT you are trying to do

7

Thank you both.
Scott, I will think about the date range limit.
As you can imagine, data sometimes defies logic and you get two dates (for say OrdDate) close together or dates very far apart (OrdDate to Tdate > 14).
But maybe a combo of the date range and my rank might help me.
Thanks again

8

hi

please excuse me if i am offending you

it always helps the other person understand if you can explain with data

this is a PHD subject by itself

image
image752×472 7.49 KB