• bro
  • Member
  • Offline

Topic: SQL query code

Hi there!

Would you mind posting the SQL query code that is used in summary panel to show account list with their final balances?

  • Slavik
  • Slavik
  • Administrator
  • Offline

Re: SQL query code

Hi Bro,
I am affraid it is not simple SQL query. I do it by many steps:
a) select all accounts (suitable to the filter) with its starting amount,
b) read sums of all credits / debits / transfers belonging to this accounts (and suitable to the filter too),
c) enumerate its final balances (amount).

Fortunatelly - computer makes it all in a few miliseconds. smile
Do you still want to know this SQL queries (with parts of Delphi code)? big_smile

  • bro
  • Member
  • Offline

Re: SQL query code

Yes, please. It will be a good starting point for making a pure SQLite query.
At first I wanted to write the query myself but thought that maybe you already had it written, so decided to ask first.
Any SQL query examples for RQ Money are welcome.

Thanks in advance!

  • Slavik
  • Slavik
  • Administrator
  • Offline

Re: SQL query code

I send you code via e-mail.

  • bro
  • Member
  • Offline

Re: SQL query code

Thanks, It was helpful.

It helped me compose these few useful query examples:

Summary about currencies:

SELECT Currency, SUM(Amount) as Amount FROM
(

SELECT u_code as Currency, a_startsum as Amount
FROM currencies JOIN accounts on (u_code=a_currency)

UNION ALL

SELECT u_code as Currency, case when c_kind IN ("+", "p") then d_sum else -d_sum end as Amount
FROM
    data
JOIN
    categories ON (d_category = c_id),
    accounts ON (d_account = a_id),
    currencies ON (u_code=a_currency)
)
GROUP BY Currency

Total balance:

SELECT
(SELECT SUM(a_startsum) FROM accounts)
+ SUM(case when c_kind IN ("+", "p") then d_sum else -d_sum end) as [Final balance]
FROM data
JOIN categories ON (d_category = c_id),
accounts ON (d_account = a_id)

Total balance (ignoring archived accounts):

SELECT
(SELECT SUM(a_startsum) FROM accounts WHERE a_archive = '0')
+ SUM(case when c_kind IN ("+", "p") then d_sum else -d_sum end) as [Final balance]
FROM data
JOIN categories ON (d_category = c_id),
accounts ON (d_account = a_id)
WHERE a_archive = '0'

If somebody uses more than one currency then the first query is helpful, but not the other two because they don't take into account the fact that different currencies have different worth. So you end up with useless number as a total "Final balance". Same issue is in the main window in RQ Money.