Joining 3 tables and getting zero values also (sql server)

Question

I'm a bit new in SQL Server and my goal is to create a view by joining 3 tables

Just a little context: At first, I only need to join two tables (fact table has dates) and my main goal is to get the week number and the corresponding values using the WEEKPART. However, I noticed that there might be a case where the first week of the year won't start on Jan 1. And using SET DATEFIRST on a view is impossible so I decided to have a calendar table and join it with the other two tables.

I have the following tables, Records and Book_Types:

Create table Book_Type(
  ID int IDENTITY  primary key,
  Name varchar(100)
);

create table Records(
  ID int IDENTITY  primary key,
  PurchDate date,
  BookName varchar(100),
  Book_TypeID int references Book_Type(id),
  Amt decimal(5,2)
);

insert into Book_Type values ('A'), ('B'), ('C'), 
                                ('D'), ('E');
insert into Records values  
                            ('20200107', 'Book1', 1, 56.23),
                            ('20200401', 'Book 99', 3, 195.09),
                            ('20200917', 'Book 54', 4, 522.9),
                            ('20200109', 'Book 34', 5, 604.32),
                            ('20200106', 'Book 22', 1, 881.4),
                            ('20200222', 'Book 14', 1, 355.66),
                            ('20200924', 'Book 70', 1, 920.44),
                            ('20201129', 'Book 12', 1, 344.86),
                            ('20200220', 'Book 38', 3, 676.5);

And I also have a Calendar table from @WraithNath's comment (the advanced calendar table, with week numbers, I copied it).

When I inner join the three tables, I get the ff results: Code:

select 
c.Week,
c.Year,
b.Name as BookType,
SUM(a.Amt) as TotalAmt
from Records as a
inner join
Book_Type as b
on b.ID = a.Book_TypeID
inner join 
Testdb.Auxiliary.Calendar as c
on
c.Day = DAY(a.PurchDate) and
c.Month = MONTH(a.PurchDate) and
c.Year = YEAR(a.PurchDate)
group by c.Week, c.Year, b.Name
order by c.Week, c.Year, b.Name asc

Results:

 Week   Year    BookType    TotalAmt
    2       2020    A           937.63
    2       2020    E           604.32
    8       2020    A           355.66
    8       2020    C           676.50
    14      2020    C           195.09
    38      2020    D           522.90
    39      2020    A           920.44
    48      2020    A           344.86

I'm okay with the results however, what I want is to also display the weeks with BookType with zero values. I'm thinking of still getting the inner join and having a union all with a left outer join (the results that displays zero values) but I don't know how to get that.

My expected would be:

Week    Year    BookType    TotalAmt
1       2020    A           0
1       2020    B           0
1       2020    C           0
1       2020    D           0
1       2020    E           0
2       2020    A           937.63
2       2020    B           0
2       2020    C           0
2       2020    D           0
2       2020    E           604.32
3       2020    A           0
3       2020    B           0
3       2020    C           0
3       2020    D           0
3       2020    E           0
4       2020    A           0
4       2020    B           0
4       2020    C           0
4       2020    D           0
4       2020    E           0
5       2020    A           0
5       2020    B           0
5       2020    C           0
5       2020    D           0
5       2020    E           0
6       2020    A           0
6       2020    B           0
6       2020    C           0
6       2020    D           0
6       2020    E           0
7       2020    A           0
7       2020    B           0
7       2020    C           0
7       2020    D           0
7       2020    E           0
1       2020    A           0
8       2020    A           355.66
8       2020    B           0
8       2020    C           676.50
8       2020    D           0
8       2020    E           0
and so on, so forth..
14      2020    C           195.09
38      2020    D           522.90
39      2020    A           920.44
48      2020    A           344.86

I need to get the type and weeks with zero values because once I achieved this, I have to add them together. In order to add them seamlessly, there must a be a line even if the TotalAmt is 0.

Any help is appreciated. Thank you!

Answer 1

You want a cross join between the calendar table and the book types. That generates the rows. Then use a left join to bring in the existing data and aggregate:

select c.Week, c.Year, b.Name as BookType,
       SUM(r.Amt) as TotalAmt
from Testdb.Auxiliary.Calendar c cross join
     Book_Type b left join
     Records r
     on b.ID = r.Book_TypeID and
        c.Day = DAY(r.PurchDate) and
        c.Month = MONTH(r.PurchDate) and
        c.Year = YEAR(r.PurchDate)
group by c.Week, c.Year, b.Name
order by c.Week, c.Year, b.Name asc;

I would expect a "Calendar" table to have a date column, so you can just join on the date rather than the date parts.

Also, in English, r is a much more reasonable table alias for a table called Records. Of course, in your actual database, a might be a reasonable alias as well. The key idea is to use table abbreviations, not arbitrary letters.

Answer 2

Change the order of what you're doing, using BookType and Calendar to create a 'template' of all the rows you want, then LEFT JOIN the Records on the that template.

SELECT
  c.Week,
  c.Year,
  b.Name as BookType,
  COALESCE(SUM(a.Amt), 0)   AS TotalAmt
FROM
  Testdb.Auxiliary.Calendar as c
CROSS JOIN
  Book_Type as b
LEFT JOIN
  Records as a
    ON  b.ID     = a.Book_TypeID
    AND c.[Date] = a.PurchDate
WHERE
      c.[Date] >= '2020-01-01'
  AND c.[Date] <  '2021-01-01'
GROUP BY
  c.Week, c.Year, b.Name
ORDER BY
  c.Week, c.Year, b.Name