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.
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.
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');
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.
18 People are following this question.