SQL SERVER Join (matching rows from tables)

Question

question

lomas asked Sep 10, '19

SQL SERVER Join (matching rows from tables)

Hi I am struggling to pull data from Both Table 1 and Table 2. I need an output similar to "Output" Table.

Criteria

1. StockScanDate(Table 2) must be bigger than ScanDateOrigin(Table1).

Thanks in Advanced.

sql server 2012 joins

table.jpg (80.9 KiB)

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · Sep 10, 2019 at 08:59 AM

Thanks for providing clear data and expected output. The data creation is:

DECLARE @TableA TABLE (SerialNo CHAR(6), ScanDateOrigin DATE);
DECLARE @TableB TABLE (SerialNo CHAR(6), StockScanDate DATE);
INSERT INTO @TableA (SerialNo, ScanDateOrigin)
VALUES
('XXXXX0', '20180810'),
('XXXXX1', '20190811'),
('XXXXX4', '20190711'),
('XXXXX5', '20190811'),
('XXXXX6', '20190811'),
('XXXXX7', '20190811'),
('XXXXX1', '20190830'),
('XXXXX4', '20190812'),
('XXXXX3', '20190812');
INSERT INTO @TableB (SerialNo, StockScanDate)
VALUES
('XXXXX0', '20180815'),
('XXXXX1', '20190826'),
('XXXXX4', '20190722'),
('XXXXX1', '20190904'),
('XXXXX6', '20190824');

0 ·

Answer 1 · 2019-09-10T09:20:17Z

ThomasRushton answered Sep 10, '19

Using the sample data provided above, I have two possible solutions:

(1) using ROW_NUMBER() to put an artificial sequence on the data, and join on that - however, this is unreliable - it relies on a 1:1 mapping between Tables and between date ranges:

SELECT  a.SerialNo,
        a.ScanDateOrigin,
        B.StockScanDate,
        CASE
            WHEN B.StockScanDate IS NOT NULL THEN
                DATEDIFF(DAY, a.ScanDateOrigin, B.StockScanDate)
        ELSE
            NULL
        END AS DaysDiff
FROM
        (
            SELECT  SerialNo,
                    ScanDateOrigin,
                    ROW_NUMBER() OVER (PARTITION BY SerialNo ORDER BY ScanDateOrigin) AS rn -- need these to partition the data so we don't get duplicated rows from @TableA
            FROM    @TableA
        ) a
    LEFT JOIN
        (
            SELECT  SerialNo,
                    StockScanDate,
                    ROW_NUMBER() OVER (PARTITION BY SerialNo ORDER BY StockScanDate) AS rn  -- need these to partition the data so we don't get duplicated rows from @TableB
            FROM    @TableB
        ) B
        ON a.SerialNo = B.SerialNo
           AND  a.ScanDateOrigin < B.StockScanDate
           AND  a.rn = B.rn;

And it's inefficient.

Use this instead:

(2) Using Cross Apply to get the most recent stock scan date that's after the scan date origin for the serial number provided:

SELECT  a.SerialNo,
        a.ScanDateOrigin,
        b.StockScanDate,
        DATEDIFF(DAY, a.ScanDateOrigin, b.StockScanDate)
FROM
        @TableA a
    CROSS APPLY
        (
            SELECT  MIN(StockScanDate) AS StockScanDate
            FROM    @TableB bin
            WHERE
                    bin.SerialNo = a.SerialNo
                    AND bin.StockScanDate > a.ScanDateOrigin
        )       b;

There may be issues again with multiple rows in B which fit between rows in A.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

lomas commented · Sep 10, 2019 at 10:53 PM

This is Perfect. Thanks you Mate.

1 ·

question