CODEWITHSUNDEEP
sqlt-sqljoinouter-join
miltenburger
miltenburger

Reputation: 91

SQL join table with 0 values available

I'm having some troubles with finding the right joins to get the expecte output from my table. My dataset exists out of thee different tables:

Clienttable

+-----------+------------+------------+-------------+--------+
| Client_No | Start_Date |   End_Date | YearOfBirth |  City  |
+-----------+------------+------------+-------------+--------+
|     1     |  1-1-2018  |    null    |    1962     |    A   |
+-----------+------------+------------+-------------+--------+
|     2     |  10-4-2016 |    null    |    1987     |    B   |
+-----------+------------+------------+-------------+--------+
|     3     | 31-12-2015 |    null    |    1992     |    A   |
+-----------+------------+------------+-------------+--------+
|     4     |  1-4-2019  | 31-12-2019 |    2001     |    B   |
+-----------+------------+------------+-------------+--------+
|     5     |  1-1-2018  |    null    |    1999     |    A   |
+-----------+------------+------------+-------------+--------+

Calender table

+-----------+
|   Date    |
+-----------+
| 1-1-2019  |
+-----------+
| 1-2-2019  |
+-----------+
| 1-3-2019  |
+-----------+
| 1-4-2019  |
+-----------+
| ........  |
+-----------+
| 1-12-2020 |
+-----------+

YearOfBirth table

+--------+
|  Year  |
+--------+
|  1910  |
+--------+
|  1911  |
+--------+
|  ....  |
+--------+
|  2020  |
+--------+

What I want ia a table with the amount of people living in every city counted by YearOfBirth. But I want it to count again for every date in my calender. It also has to show the amount if it's 0 for a YearOfBirth. The query I got so far:

SELECT a.City, a.YearOfBirth, c.Date, 
       (SELECT COUNT(DISTINCT(b.ClientNo))
        FROM Client as b
        WHERE b.Start_Date < c.Date
        AND (b.End_Date > c.Date OR b.End_Date is null)
        AND a.City = b.City
        AND a.YearOfBirth = b.YearOfBirth) as Amount
FROM Client as a
FULL OUTER JOIN Calender as c
ON a.Start_Date <= c.Date
AND b.Start_Date >= c.Date
FULL OUTER JOIN YearOfBirth as d
ON a.YearOfBirth = d.YearOfBirth
GROUP BY a.City, a.YearOfBirth, c.Date

The query works kind of fine, but i miss all the years which are count 0. Any idea on how to fix this?

Expected output:

+------+----------+-------------+--------+
| City |   Date   | YearOfBirth | Amount |
+------+----------+-------------+--------+
|  A   | 1-1-2019 |    1910     |    0   |
+------+----------+-------------+--------+
|  A   | 1-1-2019 |    1911     |    0   |
+------+----------+-------------+--------+
|  A   | 1-1-2019 |    1912     |    0   |
+------+----------+-------------+--------+
|  A   | 1-1-2019 |    1962     |    1   |
+------+----------+-------------+--------+

I'm not putting all the records in, because I expect for every city a record per date with the amount of people born in a specific year, also when it's 0.

Upvotes: 0

Views: 557

Answers (1)

George Joseph
George Joseph

Reputation: 5922

I would do the following manner.

Get the list of all possible combinations of city, year_of_birth and dates using a cartesian join.

After that i just need to compare the values with the entries in clienttable on the basis of whether the city,year_of_birth, and date matches the start and end date followed by grouping.

So in the case of missing clientid, they would be considered as null which would show up as 0

with data
  as (select c.city,a.year_of_birth,b.date
        from YearOfBirth a
        join calendar b
          on 1=1
        join (select distinct city
                from clienttable
             )c
          on 1=1 
      )
 select m.city 
       ,m.date
       ,m.year_of_birth
       ,count(clientid) as amount
   from data m
left join clienttable n
     on m.city=n.city
    and m.year_of_birth=n.year_of_birth
    and m.date between n.start_date and isnull(n.end_date,'3000-12-31')
group by m.city 
       ,m.date
       ,m.year_of_birth

Upvotes: 1

Related Questions