2019. 12. 28. 13:07 keltezéssel, Csanyi Pal írta:
Hi,
I have attached here the exported sql file of my little sqlite database.
I am a newbe in the sqlite language so I ask here only an advice in
which direction to go, eg. which functions of sqlite language to use to
achieve my goal?
My issue is the following.
So far my database has two tables only: the Earnings, and the Expenses
table only.
I have two goals here:
1. goal is to calculate the Average of Expenses BETWEEN Dates From I.
Earnings To Date minus 1 Day before II. Earnings in a Month;
2. goal is to calculate the Average of Expenses BETWEEN Dates From II.
Earnings in a Month To Date minus 1 Day before I. Earnings in The Next
Month.
I am thinking like this regarding my 1. goal:
---------------------------------------------
The task of this View is described below.
Find the expenses entered that are between the following two dates:
within one month from the date of the I. earnings to the
day before the day of the II. earnings.
That is, based on the data entered,
1. Step: in this step find from records
starting on the I. earnings date of the first month
up to the day before the second earnings date of the same month, then
sum all these expenses, and get the first partial result, which I
call here E1.
Then continue with the same
in the second step, the next one, ie with the
2. Step: starting on the I. earnings date of the second month
up to the day before the second earnings date of the same month, then
gets the partial result E2.
3., 4., n. Steps..
Then continue with the same steps until it arrives
until this month - if there exists the II. Earnings in the Month, or
until the Month before this Month ( in which there is surtenly the
II. Earnings),
and calculate the En result for it.
When it have completed these steps, calculate the average of the
partial results E1, E2, ..., En.
Do all this with a single View.
I am thinking like this regarding my 2. goal:
---------------------------------------------
The task of this View is described below.
Find the expenses entered that are between the following two dates:
within one month from the date of the II. earnings to the
day before the day of the I. earnings in the Next Month.
That is, based on the data entered,
1. Step: in this step find from records
starting on the II. earnings date of the first month
up to the day before the first earnings date of the following (
second ) month, then
sum all the expenses, and get the first partial result, which I call
here E1.
Then continue with the same
in the second step the next one, like:
2. Step: starting on the II. earnings date of the second month
up to the day before the first earnings date of the following (
third ) month
and gets the partial result E2.
3., 4., n. Steps..
Then continue with the same steps until it arrives
until the month before this month, and calculate the En result for it.
When it have completed these steps, calculate the average of the
partial results E1, E2, ..., En.
Do all this with a single View.
Do I think well?
Any advices will be appreciated!
If one can't find the attachment mentioned in the original mail, then
here is the database in text:
CREATE TABLE Expenses (Id INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE,
Date DATE, Expense REAL);
CREATE TABLE Earnings (Id INTEGER PRIMARY KEY ASC ON CONFLICT REPLACE,
Date DATE, Earning REAL, Part TEXT);
CREATE VIEW
"AVG_of_Expenses_BETWEEN_Dates_From_I._Earnings_To_Date_minus_1_Day_before_II._Earnings_in_a_Month"
AS SELECT "Date", "Expense" FROM Expenses WHERE EXISTS(SELECT * FROM
Earnings WHERE Earnings.Date = Expenses.Date)
/* The task of this View is described below.
Find the expenses entered that are between the following two dates:
within one month from the date of the I. earnings to the
day before the day of the II. earnings.
That is, based on the data entered,
1. Step: in this step find from records
starting on the I. earnings date of the first month
up to the day before the second earnings date of the same month, then
sum all these expenses, and get the first partial result, which I
call here E1.
Then continue with the same
in the second step, the next one, ie with the
2. Step: starting on the I. earnings date of the second month
up to the day before the second earnings date of the same month, then
gets the partial result E2.
3., 4., n. Steps..
Then continue with the same steps until it arrives
until this month - if there exists the II. Earnings in the Month, or
until the Month before this Month ( in which there is surtenly the
II. Earnings),
and calculate the En result for it.
When it have completed these steps, calculate the average of the
partial results E1, E2, ..., En.
Do all this with a single View.
*/
/*
"AVG_of_Expenses_BETWEEN_Dates_From_I._Earnings_To_Date_minus_1_Day_before_II._Earnings_in_a_Month"(Date,Expense)
*/;
CREATE VIEW
"AVG_of_Expenses_BETWEEN_Dates_From_II._Earnings_in_a_Month_To_Date_minus_1_Day_before_I._Earnings_in_The_Next_Month"
AS SELECT "Date", "Expense" FROM Expenses
/* The task of this View is described below.
Find the expenses entered that are between the following two dates:
within one month from the date of the II. earnings to the
day before the day of the I. earnings in the Next Month.
That is, based on the data entered,
1. Step: in this step find from records
starting on the II. earnings date of the first month
up to the day before the first earnings date of the following (
second ) month, then
sum all the expenses, and get the first partial result, which I call
here E1.
Then continue with the same
in the second step the next one, like:
2. Step: starting on the II. earnings date of the second month
up to the day before the first earnings date of the following (
third ) month
and gets the partial result E2.
3., 4., n. Steps..
Then continue with the same steps until it arrives
until the month before this month, and calculate the En result for it.
When it have completed these steps, calculate the average of the
partial results E1, E2, ..., En.
Do all this with a single View.
*/
/*
"AVG_of_Expenses_BETWEEN_Dates_From_II._Earnings_in_a_Month_To_Date_minus_1_Day_before_I._Earnings_in_The_Next_Month"(Date,Expense)
*/;
--
Best, Pali
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
