Hi,
I have a simple database, the 'Incomes_Expenses.db' on my system.
It's SQL is here:
<the database>
--
-- File generated with SQLiteStudio v3.2.1 on P nov. 15 20:39:18 2019
--
-- Text encoding used: UTF-8
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
-- Table: Expenses
CREATE TABLE Expenses (Id INTEGER PRIMARY KEY, Date DATE, Expense REAL,
Currency TEXT);
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (1,
'2019-10-15', 421.35, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (2,
'2019-10-18', 560.5, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (3,
'2019-10-19', 37.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (4,
'2019-10-20', 632.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (5,
'2019-10-21', 124.5, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (6,
'2019-10-23', 1200.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (7,
'2019-10-31', 278.43, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (8,
'2019-11-01', 250.3, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (9,
'2019-11-02', 429.72, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (10,
'2019-11-03', 310.11, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (11,
'2019-11-04', 197.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (12,
'2019-11-05', 257.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (13,
'2019-11-06', 4230.0, '$');
INSERT INTO Expenses (Id, Date, Expense, Currency) VALUES (14,
'2019-11-08', 452.38, '$');
-- Table: Incomes
CREATE TABLE Incomes (Id INTEGER PRIMARY KEY, Date DATE, Income REAL,
Currency TEXT);
INSERT INTO Incomes (Id, Date, Income, Currency) VALUES (1,
'2019-11-04', 1573.0, 'USD');
-- View: Sum of Expenses Beteen two Dates
CREATE VIEW "Sum of Expenses Beteen two Dates" AS SELECT Date,
CASE
WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04',
'-1 days') THEN SUM(Expense)
ELSE 'Sorry, it is not between the two dates.'
END as 'Sum of Expenses:'
FROM Expenses;
COMMIT TRANSACTION;
PRAGMA foreign_keys = on;
</the database>
I wish to SUM some Expenses which meets some condition.
The condition follows.
The Date of an Expense must be between two dates.
The CASE statement should add to the SUM the value of an Expense if and
only if the Date of an Expense is BETWEEN two dates.
The two dates are like this:
the 1. Date is in the previous month, and
the 2. Date is in the Incomes Table and is in this month minus 1 day.
Eg. 1. Date is say date('now', '-1 months')
and 2. Date is say date('2019-11-04').
So I tried with this Query to get the SUM of Expenses between 1. and 2.
Date like this:
<code>
SELECT Date,
CASE
WHEN Date BETWEEN date('now', '-1 months') AND date('2019-11-04',
'-1 days') THEN 'It is between the two dates.'
ELSE 'Sorry, it is not between the two dates.'
END as 'Information:'
FROM Expenses
</code>
This shows only wether is a Date between two dates or is not.
The folloing code shows some results, but the resulted Sum is not valid:
<code>
sqlite3 Incomes_Expenses.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> SELECT * FROM 'Sum of Expenses Beteen two Dates';
2019-10-14|Sorry, it is not between the two dates.
2019-10-15|10132.7
2019-10-18|10132.7
2019-10-19|10132.7
2019-10-20|10132.7
2019-10-21|10132.7
2019-10-23|10132.7
2019-10-31|10132.7
2019-11-01|10132.7
2019-11-02|10132.7
2019-11-03|10132.7
2019-11-04|Sorry, it is not between the two dates.
2019-11-05|Sorry, it is not between the two dates.
2019-11-06|Sorry, it is not between the two dates.
2019-11-08|Sorry, it is not between the two dates.
sqlite>
</code>
It should gives this result: 4440,91 $ but not the 10132.7 $.
Can one uses the CASE statement to get a SUM of Expenses which Dates are
between two given dates?
--
Best, Pali
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
