SQL Bakery Report

I'm trying to write SQLite code to print a report in the form of the one listed below. Once I've generated the query, I will write a simple Python script to organize the information in the same fashion as below. The trouble that I'm having is to determine the query.

---

The database scheme I have is as follows:

Customer (customerID, lastName, firstName, age, gender, street, city, state, favorites, lastVisit)

LineItem (receiptID, lineNum, productID) Product (productID, flavor, kind, price)

ProductXSale(productID, saleID)

Rating (customerID, productID, score, comment)

Receipt (receiptID, purchaseDate, customerID)

Sale (saleID, startDate, endDate, percentOff)

---

I don't think we will need the Rating or Customer tables when building the query.

The hard part for me is determining the number of a product X on a certain day. To calculate this, we need to check each receipt on date Y and count how many times X appears on each receipt.

Another issue is if there is a sale happening at a point in time. So if product X goes on sale, then we need to report it at the reduced price.

Naturally, the income for each column is calculated by multiplying the number of a product sold by its price (checking to see if there is a sale).

Model report:

SALES REPORT FOR year-month

Product.id  Product.flavor  Product.kind  Product.price

    SALE DATE            NBR SOLD       INCOME
    date-of-purchase     number-sold    total-amount
    date-of-purchase     number-sold    total-amount
    date-of-purchase     number-sold    total-amount
    ⋮
    date-of-purchase     number-sold    total-amount

             TOTALS:  total-nbr-sold     total-total

Product.id  Product.flavor  Product.kind  Product.price

    SALE DATE            NBR SOLD       INCOME
    date-of-purchase     number-sold    total-amount
    date-of-purchase     number-sold    total-amount
    date-of-purchase     number-sold    total-amount
    ⋮
    date-of-purchase     number-sold    total-amount

             TOTALS:  total-nbr-sold     total-total

⋮

    GRAND TOTALS:            1234          999.99