CODEWITHSUNDEEP
sqlsql-server
zesla
zesla

Reputation: 11793

Join two table by dates and populate the missing data when one table has missing dates

In SQL Server, I have a two tables (tbl1 and tbl2) as shown below. tbl2 contains a list of all dates. In tbl1, some of the dates are missing.

tbl1

name    nameid  count   date
-----------------------------------------------
apple     1      77     2019-07-29 00:00:00.000
orange    2     129     2019-07-29 00:00:00.000
apple     1     399     2019-08-05 00:00:00.000
orange    2      27     2019-08-05 00:00:00.000
apple     1     122     2019-08-12 00:00:00.000
orange    2       5     2019-08-19 00:00:00.000

tbl2

    timeid     date
5   2019-07-29 00:00:00.000
4   2019-08-05 00:00:00.000
3   2019-08-12 00:00:00.000
2   2019-08-19 00:00:00.000
1   2019-08-26 00:00:00.000

What I need is the table below. I need to join the two table, populate the name and nameid for the missing dates, and set those counts as 0. 

date                    name nameid count   timeid
---------------------------------------------------
2019-07-29 00:00:00.000 apple   1     77    5
2019-07-29 00:00:00.000 orange  2     129   5
2019-08-05 00:00:00.000 apple   1     399   4
2019-08-05 00:00:00.000 orange  2     27    4
2019-08-12 00:00:00.000 apple   1     122   3
2019-08-12 00:00:00.000 orange  2     0     3
2019-08-19 00:00:00.000 apple   1     0     2
2019-08-19 00:00:00.000 orange  2     5     2
2019-08-26 00:00:00.000 apple   1     0     1
2019-08-26 00:00:00.000 orange  2     0     1

I did the following:

SELECT t1.date, name, nameid, count, timeid 
FROM tbl2 t1
LEFT JOIN tbl1 t2 ON t1.date = t2.date

and got this output:

date                    name     nameid    count    timeid
----------------------------------------------------------
2019-07-29 00:00:00.000 apple       1        77      5
2019-07-29 00:00:00.000 orange      2       129      5
2019-08-05 00:00:00.000 apple       1       399      4
2019-08-05 00:00:00.000 orange      2        27      4
2019-08-12 00:00:00.000 apple       1       122      3
2019-08-19 00:00:00.000 orange      2         5      2
2019-08-26 00:00:00.000 NULL        NULL    NULL     1

It does not seem to be right. Does anyone know how I can generate the desired joined table?

Below is the code to generate tbl1 and tbl2

DROP TABLE IF EXISTS tbl1

CREATE TABLE tbl1 
(
    name VARCHAR (50) NOT NULL,
    nameid INT NOT NULL,
    count INT NOT NULL,
    date DATETIME
);

INSERT INTO tbl1(name, nameid, count, date)
VALUES ('apple', 1, 77, '2019-07-29'),
        ('orange', 2, 129, '2019-07-29'),
        ('apple', 1, 399, '2019-08-05'),
         ('orange', 2, 27, '2019-08-05'),
         ('apple', 1, 122, '2019-08-12'),
         ('orange', 2, 5, '2019-08-19');

DROP TABLE IF EXISTS tbl2

CREATE TABLE tbl2 
(
    timeid INT NOT NULL,
    date DATETIME
);

INSERT INTO tbl2(timeid, date)
VALUES (5,'2019-07-29'),
        (4,'2019-08-05'),
         (3,'2019-08-12'),
         (2,'2019-08-19'),
         (1,'2019-08-26');

Upvotes: 1

Views: 543

Answers (1)

Nick
Nick

Reputation: 147166

To achieve your desired result, you need to cross join the distinct name values from tbl1 with the dates from tbl2 and then LEFT JOIN that to tbl1 again, using COALESCE to replace NULL values of count with 0:

SELECT t2.date, n.name, n.nameid, COALESCE(t1.count, 0) AS count, t2.timeid
FROM tbl2 t2
CROSS APPLY (SELECT DISTINCT name, nameid FROM tbl1) n
LEFT JOIN tbl1 t1 ON t1.date = t2.date AND t1.name = n.name
ORDER BY t2.date, n.name

Output:

date                        name    nameid  count   timeid
2019-07-29 00:00:00.000     apple   1       77      5
2019-07-29 00:00:00.000     orange  2       129     5
2019-08-05 00:00:00.000     apple   1       399     4
2019-08-05 00:00:00.000     orange  2       27      4
2019-08-12 00:00:00.000     apple   1       122     3
2019-08-12 00:00:00.000     orange  2       0       3
2019-08-19 00:00:00.000     apple   1       0       2
2019-08-19 00:00:00.000     orange  2       5       2
2019-08-26 00:00:00.000     apple   1       0       1
2019-08-26 00:00:00.000     orange  2       0       1

Demo on dbfiddle

Upvotes: 2

Related Questions