1

I am a newbie in SQL and need help with the joining of these 2 tables on the closest date. Thanks in Advance!

Table A

billing_start_date id status
2020-07-27 100 active
2018-12-27 101 inactive

Table B

period_end_date id A B
2018-12-21 100 4 5
2018-12-28 100 2 3
2020-07-24 101 3 4
2020-07-31 101 2 3

Output

period_end_date id period_end_date status A B
2018-12-27 100 2018-12-28 active 2 3
2020-07-27 101 2020-07-24 inactive 3 4
4
  • What have you tried so far? Also, can you explain how you got your output? Sep 13 '21 at 15:05
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Sep 13 '21 at 16:53
  • Hi, and welcome to dba.se! Could you please go to dbfiddle.uk and create your tables and sample data and maybe include a bit more of an explanation of your requirements... Sep 13 '21 at 19:11
  • Also, please always include your server version with your questions! Sep 13 '21 at 19:32
1

You could try this - it makes the assumption that billing_start_date, id and status will remain the same.

I've used MySQL 8 to do this - previous versions have problems with GROUP BY (Google ONLY_FULL_GROUP_BY to find out about this - it's become the default in version 8, so no risk of being tripped up by this particularly egregious bug).

All of the code below is available on the fiddle here.

First, I started by creating the tables and records as per your data (this is really your job - it eliminates duplication of effort and provides a single source of truth):

CREATE TABLE billing_start  -- your table_a - I try to give my tables meaningful names!
(
  billing_start_date DATE NOT NULL,
  id                 SMALLINT NOT NULL,
  status             VARCHAR (25) NOT NULL
);

and:

INSERT INTO billing_start VALUES
('2020-07-27', 100, 'active'),
('2018-12-27', 101, 'inactive');

and the other table:

CREATE TABLE period_end
(
  period_end_date DATE NOT NULL,
  id              SMALLINT UNSIGNED NOT NULL,
  code_a          TINYINT  UNSIGNED NOT NULL,
  code_b          TINYINT  UNSIGNED NOT NULL
);

and:

INSERT INTO period_end VALUES
('2018-12-21',  100,    4,  5),
('2018-12-28',  100,    2,  3),
('2020-07-24',  101,    3,  4),
('2020-07-31',  101,    2,  3);

Then, I pick out the date differences as follows:

SELECT 
  b.billing_start_date AS bsd, b.id AS bid, b.status,
  ABS(p.period_end_date - b.billing_start_date) AS diff
FROM billing_start b
JOIN period_end    p
  ON b.id = p.id;

Result:

bsd     bid     status  diff
2020-07-27  100     active  19506
2020-07-27  100     active  19499
2018-12-27  101     inactive    19497
2018-12-27  101     inactive    19504

Note the use of the ABS() function... it's required to get the smallest gap in the dates!

Then I use the above result in a subquery to get the billing_start table records with a given id and status which match the criteria! But I hold onto the differences in date so I can JOIN back to the period_end table using these dates as the JOINing criterion.

SELECT bsd, bid, bstat, MIN(diff) AS mdiff
FROM
(
  SELECT 
    b.billing_start_date AS bsd, b.id AS bid, b.status AS bstat,
    ABS(p.period_end_date - b.billing_start_date) AS diff
  FROM billing_start b
  JOIN period_end    p
    ON b.id = p.id  
) AS tab1
GROUP BY bsd, bid, bstat;

Result:

bsd     bid     bstat   mdiff
2020-07-27  100     active  19499
2018-12-27  101     inactive    19497

Finally, I join back to the period_end table as explained above:

SELECT 
  t.bsd, t.bid, t.bstat, p.period_end_date, p.id, mdiff,
  p.period_end_date - t.bsd, p.code_a, p.code_b
FROM
(
  SELECT bsd, bid, bstat, MIN(diff) AS mdiff
  FROM
  (
    SELECT 
      b.billing_start_date AS bsd, b.id AS bid, b.status AS bstat,
      ABS(p.period_end_date - b.billing_start_date) AS diff
    FROM billing_start b
    JOIN period_end    p
      ON b.id = p.id  
  ) AS tab1
  GROUP BY bsd, bid, bstat
) AS t
JOIN period_end p
  ON  t.bid = p.id
  WHERE ABS(p.period_end_date - t.bsd) = ABS(mdiff)
ORDER BY t.bid;

Result:

bsd        bid  bstat   period_end_date id  mdiff   p.period_end_date - t.bsd   code_a  code_b
2020-07-27 100  active   2018-12-28     100  19499  -19499                   2  3
2018-12-27 101  inactive 2020-07-24     101  19497   19497                   3  4

Et voilà! The desired result!

I left the date differences in (and in the fiddle) to show my line of thinking. Tricky enough question - +1! But next time, please put in your server version and please go to the trouble of constructing a fiddle - help us to help you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.