A query for results based on date range

Question

I have a table called invoice and I have a query that will give me the sum of all invoice amounts for a given account where the invoice has a status of PAID between a certain date range.

Below is the schema and query.

Schema:

CREATE TABLE invoice (
    id             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    agreement      BOOLEAN NOT NULL DEFAULT 0,
    invoice_number VARCHAR(255) NOT NULL,
    status         VARCHAR(255) NOT NULL INDEX,
    status_text    VARCHAR(255) NOT NULL,
    currency       VARCHAR(255) NOT NULL,
    amount         INT(10) UNSIGNED NOT NULL,
    description    VARCHAR(500) NOT NULL,
    --
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP INDEX,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    --
    PRIMARY KEY (id),
    UNIQUE (invoice_number)
);

CREATE TABLE account_invoice (
    account_id INT(10) UNSIGNED NOT NULL,
    invoice_id INT(10) UNSIGNED NOT NULL,
    --
    INDEX (invoice_id, account_id),
    PRIMARY KEY (account_id, invoice_id),
    FOREIGN KEY (account_id) REFERENCES account (id) ON DELETE CASCADE,
    FOREIGN KEY (invoice_id) REFERENCES invoice (id) ON DELETE CASCADE
);

Query:

SELECT SUM(invoice.amount)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"

Let's assume our date range starts on Jan 01 2018, ends on Jan 02 2018.

Let's also assume I have five invoices:

1. updated_at is Jan 01 2018, amount is 1000
2. updated_at is Jan 01 2018, amount is 2000
3. updated_at is Jan 02 2018, amount is 50
4. updated_at is Jan 03 2018, amount is 100
5. updated_at is Jan 04 2018, amount is 100

Given the data above, the query above will give me a result of 3050. Perfect.

Now what I want to do is take the date range, and get a list, as opposed to the SUM(amount) of matching invoices. For each invoice matched against the query, I want to take its date and amount.

As such, the result set should look like this:

[
   {value: 1000, date: "Jan 01 2018"},
   {value: 2000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

Using that result set, I want to be able to make a second condition to shrink down the result set further based on on an interval of time, e.g. daily, weekly, monthly, yearly.

So if I use a daily interval from the result set above, I should get this:

[
   {value: 3000, date: "Jan 01 2018"},
   {value: 50,   date: "Jan 02 2018"}
]

If I use a weekly interval, I should get this:

[
   {value: 3050, date: "Jan 01 2018"},
]

...and so on. As you see it just essentially does the sum logic of my first query on the result set of my second query.

My current attempt at the first part (no interval logic) is the following query:

SELECT invoice.amount AS value, invoice.updated_at AS date
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at <= ?
    AND invoice.status = "PAID"
GROUP BY invoice.id

This does not give me the result I'm looking for, so I can't hope to move to the second part with intervals yet. Any guidance would be appreciated.

UPDATE:

SELECT invoice.amount, %s AS "invoice.updated_at"
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL
    AND invoice.updated_at >= ? 
    AND invoice.updated_at < ?
    AND invoice.status = "PAID"
GROUP BY invoice.id, invoice.amount, FLOOR(TO_DAYS(invoice.updated_at) - 2) / 7

Answer 1

Your first resultset comes from

... WHERE updated_at >= ...
      AND updated_at <  ...

Your second resultset comes from adding this on

... GROUP BY DATE(updated_at)

The third ("weekly") gets trickier:

... GROUP BY FLOOR(TO_DAYS(updated_at) - 2) / 7

(Where the "-2" is adjusted to make a "week" start on Sunday or Monday or whatever you prefer.)

Yearly:

... GROUP BY YEAR(updated_at)

Monthly:

... GROUP BY LEFT(updated_at, 7)

(because the LEFT will give you something like the string "2011-03").