Advanced (?) SQL Joins?

Question

I am a bit lost as to how to explain this, so I will try to give an example of some tables (+ data) and then the result that I am after (all my table columns are NOT NULL):

Table: Customers

Id int primary key
Name varchar(250)

Table: Stats (Date, CustomerId is the primary key)

Date (date)
CustomerId (int) - foreign key to Customers table
Earning (money)

Table: Bonus

Id int primary key
CustomerId int - foreign key to Customers table
Date date
Amount money

Table: Payments

Id int primary key
DateFrom date,
DateTo date,
CustomerId bigint - foreign key to Customers table

Table: CampaignPayment

Id int primary key
PaymentId int - foreign key to payments table
Quantity int
UnitPrice money

Table: BonusPayment

Id int primary key
PaymentId int - foreign key to payments table
Amount money

The idea here is that everytime a customer does something that is supposed to earn them money, it goes into the stats table. Customers can also receive different kinds of bonuses which goes into the bonus table. Every so often I need to create an invoice for the customers (Payments table) which will list the stuff from the stats table + the bonus table within the specified time period and that will generate the invoice (that is the payments table defines who the invoice is for, which period and the campaignpayment and bonuspayment table defines what is being paid and why).

Now - I need to be able to join all these tables up to be able to get an output of the following:

CustomerId | CustomerName | PaymentId | Amount | BonusAmount | DateFrom | DateTo

Amount is the summed Amount ( SUM(Quantity * UnitPrice) ) from the CampaignPayment table, and BonusAmount is the summed Amount ( SUM(Amount) ) from the BonusPayment table. DateFrom and DateTo is from the Payments table.

The trick is that for every customer within a given month where every single day of that month is not covered, I want a row with the following data:

CustomerId | CustomerName | NULL | (Stats.Earning - Amount Earned from possible payments within the month) | (Bonus.Amount - Amount Earned possible bonuses that is in payments within the month) | First day of month | Last day of month

I may need a bit more of complex logic as to how to calculate the amount and bonus amount within these "empty" rows but as for now, that is what I need to begin with.

How would I go about this? I know how to get the "initial" bit done, but how would I go about adding in these "empty" rows? I hope I explained the problem well enough in detail and that you can see the idea here - if not let me know and I will try to explain further.

The database is MS SQL Server 2008.

EDIT: Also alternatively an "empty" row for every customer per month is also and acceptable solution.

Answer 1

I'd make an auxiliary table with "every single day of that month" to ease identifying if "every single day of the month is not covered" (a somewhat ambiguous spec, but the aux table should help whether you mean "no day is covered" or "some days are not covered" and whether a day is considered "covered" if it has either a bonus or stats, or if it needs to have both to be considered "covered" -- these ambiguities are why I'm not going to even try and sketch the SQL using this aux table;-). Then I'd UNION the "empty rows" to the "initial bit" that you already know how to get done -- seems a perfect task for UNION!-)