MySQL, чтобы получить ежедневный приток / отток на основе счетов?

Во-первых, мой стол:

mysql> desc invoice;
+-------------+-----------------------+------+-----+---------+----------------+
| Field       | Type                  | Null | Key | Default | Extra          |
+-------------+-----------------------+------+-----+---------+----------------+
| id          | int(11)               | NO   | PRI | NULL    | auto_increment |
| date        | timestamp             | YES  |     | NULL    |                |
| sent        | timestamp             | YES  |     | NULL    |                |
| due_date    | timestamp             | YES  |     | NULL    |                |
| amount      | float                 | YES  |     | NULL    |                |
| amount_due  | float                 | YES  |     | NULL    |                |
| status      | enum('unpaid','paid') | YES  |     | NULL    |                |
| customer_id | int(11)               | NO   | MUL | NULL    |                |
+-------------+-----------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

Может кто-нибудь помочь мне придумать запрос, который будет возвращать для каждой строки:

День (сгруппируйте по due_date) Начальное сальдо дня (Это будет конечный сальдо предыдущего дня) Приток (сумма ПОЛОЖИТЕЛЬНЫХ транзакций за день) Отток (сумма ОТРИЦАТЕЛЬНЫХ транзакций за день) Конечное сальдо (Конечное сальдо дня после применения всех транзакций притока и оттока)

В основном, каждый день я хочу знать, сколько денег было внесено, сколько денег было снято, остаток дня, с которого начался день, и конечный баланс.

Я также должен указать, что для дня я хочу сгруппировать строки по "due_date" и использовать столбец "amount" в качестве основы для всех транзакций (игнорируя amount_due).

Есть идеи?

Я мог бы пойти по пути создания другой таблицы, содержащей только эту информацию, но подумал, что, может быть, кто-то может придумать изящный запрос, который может сделать это на основе фактических данных.

Источник

2 ответа

Решение

Вот мой взгляд на это. По сути, вы присоединяетесь к таблице счетов

todaydata.due_date=DATE_ADD(yesterdaydata.due_date,DAY,-1)

так что у вас есть доступ к вчерашнему полю amount_due (я сделал левое соединение, поэтому, если вы попросите первый день в вашей таблице, вы все равно получите запись обратно)

Остальное довольно просто. Я не был уверен, было ли сегодняшнее конечное сальдо суммой вчерашнего + притока + оттока или просто суммой todaydata.amount_due, поэтому я включил оба.

SELECT 
todaydata.due_date,
SUM(case when yesterdaydata.amount_due is null then 0 else yesterdaydata.amount_due end )) AS StartingBalance,
SUM(CASE when todaydata.amount > 0 THEN todaydata.amount ELSE 0 END)  AS INFLOW,
SUM(CASE when todaydata.amount < 0 THEN todaydata.amount ELSE 0 END)  AS OUTFLOW,
SUM(yesterday.amount_due) AS StartingBalance +
            SUM(CASE where todaydata.amount > 0 THEN todaydata.amount ELSE 0 END)  + 
            SUM(CASE where todaydata.amount < 0 THEN todaydata.amount ELSE 0 END)  AS CalculatedEndingBalance,
SUM(todaydata.amount_due) AS OtherEndingBalance
FROM invoice [todaydata]
LEFT JOIN invoice [yesterdaydata] ON todaydata.due_date=DATE_ADD(yesterdaydata.due_date,DAY,-1)
GROUP BY todaydata.due_date

У меня не было возможности проверить это на базе данных MySQL, поэтому дайте мне знать о любых синтаксических ошибках, и я исправлю.

Источник

Вам нужно будет отделить ваши транзакции от таблиц счетов-фактур следующим образом:

TABLE invoice (
   id int autoincrement,
   due_date date,
   customer_id int,
   sent_date date
);

TABLE transaction (
   id int autoincrement primary key
   transaction_date date,
   amount double,
   transaction_type enum ("charge", "payment"),
   customer_id int
);

После того, как вы закончили создание таблицы транзакций, вы можете легко получить то, что вам нужно:

SELECT SUM(amount), transaction_type, transaction_date, customer_id FROM transaction GROUP BY customer_id, transaction_date, transaction_type
Источник
Другие вопросы по тегам