Select each distinct of table 1 for every value of table 2-CodePudding

I'm essentially working with three tables; first: a Month/Year calendar, second: Customer data (that does have a parent/child relationship with itself), and third: sales data. I would like to be able to show sales for each customer for each month in a date range, regardless of months where there may have been no sales for one or more customer.

I can get queries to show every month/year in my range, and calculate sales totals for months with sales. However, since the account numbers are joining to the calendar through a table that doesn't have values for every month, I can't figure out how to list the accounts with null sales.

The closest I've gotten so far:

with cumulative as (
select MONTH(s.docdate) [Month]
, YEAR(s.docdate) [Year]
, s.account [Account]
, sum(s.amount) [sales]
from sales s
group by MONTH(s.docdate), YEAR(s.docdate), s.account
)

select c.monthno [Month]
, c.year [Year]
, (select account from customers where account=s.account) [Account]
, s.sales
from cumulative s
right join calendar c
on datefromparts(s.year, s.month, 1) = datefromparts(c.year,c.monthno,1)
order by c.year, c.monthno

resulting with;

Month Year Account sales
1 2020 1 25
1 2020 2 90
2 2020 null null
3 2020 3 45
3 2020 4 65
4 2020 null null
5 2020 1 120
5 2020 2 45
6 2020 null null
7 2020 null null
etc.

example setup here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=b8ae260f2901693bf4cca75fb2451649

If I try to use a left or right join to bring in the customer table, collapses results to only months and accounts with sales values.

with cumulative as (
select MONTH(s.docdate) [Month]
, YEAR(s.docdate) [Year]
, s.account [Account]
, sum(s.amount) [sales]
from sales s
group by MONTH(s.docdate), YEAR(s.docdate), s.account
)

select c.monthno [Month]
, c.year [Year]
, c2.account [Account]
, s.sales
from cumulative s
right join calendar c
on datefromparts(s.year, s.month, 1) = datefromparts(c.year,c.monthno,1)
right join customers c2
on s.account=c2.account

order by c.year, c.monthno

gives:

Month Year Account sales
1 2020 1 25
1 2020 2 90
3 2020 3 45
3 2020 4 65
5 2020 2 45
5 2020 1 120
1 2021 2 75

Output I'd like to see:

Month Year Account sales
1 2020 1 25
1 2020 2 90
1 2020 3 null
1 2020 4 null
2 2020 1 null
2 2020 2 null
2 2020 3 null
2 2020 4 null

How can I get every account number from customer to show up for each month in calendar?

CodePudding user response：

If you combine Calendar and Customers with a cross join, you get a complete set of accounts and dates. Make that a CTE, and then use your Cumulative CTE to gather those results. Now you can select from the Calendar/Customers table with a left join to the sales data:

with BaseTable
as (
select c.MonthNo as Month
     , c.Year
     , cust.Account
from [CALENDAR] c
cross join Customers cust
), cumulative as (
select MONTH(s.docdate) [Month]
, YEAR(s.docdate) [Year]
, s.account [Account]
, sum(s.amount) [sales]
from sales s
group by MONTH(s.docdate), YEAR(s.docdate), s.account
)
select bt.Month
     , bt.Year
     , bt.Account
     , c.sales
  from BaseTable bt
left join cumulative c
on c.month = bt.Month
and c.Year = bt.Year
and c.Account = bt.Account