You're right.
I am developing an Android app on App Inventor2.
The app is in Hungarian language so the SQLite database contains
tables and columns with Hungarian names.
The whole schema is like this:
CREATE TABLE Beiratkozottak(
az INTEGER PRIMARY KEY UNIQUE,
TanuloNeve TEXT NOT NULL,
EvFolyam INTEGER NOT NULL,
Tagozat TEXT NOT NULL
);
CREATE TABLE Hianyzok(
az INTEGER PRIMARY KEY,
HianyzoTanulo TEXT NOT NULL,
Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE JelenLevok(
az INTEGER PRIMARY KEY UNIQUE,
JelenLevoTanulo TEXT NOT NULL,
Orak INTEGER REFERENCES Orak(az) NOT NULL
);
CREATE TABLE Orak(
az INTEGER PRIMARY KEY UNIQUE,
Keltezes DATE DEFAULT NULL,
OrarendNapja TEXT DEFAULT NULL,
BlokkoraSzama TEXT DEFAULT NULL,
EvFolyam INTEGER DEFAULT NULL,
Tagozat TEXT DEFAULT NULL,
OraVazlat TEXT DEFAULT NULL,
OraNaplo TEXT DEFAULT NULL,
Emlekezteto TEXT DEFAULT NULL,
OraRend INTEGER REFERENCES OraRend(az) DEFAULT NULL,
Beiratkozottak INTEGER REFERENCES Beiratkozottak(az) DEFAULT NULL,
TanSzombatokOraRendjei INTEGER REFERENCES TanSzombatokOraRendjei(az)
DEFAULT NULL,
KivetelesNapokOraRendjei INTEGER REFERENCES
KivetelesNapokOraRendjei(az) DEFAULT NULL
);
CREATE TABLE OraRend(
az INTEGER PRIMARY KEY UNIQUE,
aHetNapja TEXT,
HanyadikIskolaOra INTEGER,
EvFolyam INTEGER,
Tagozat TEXT
);
CREATE TABLE TanSzombatokOraRendjei(
az INTEGER PRIMARY KEY UNIQUE,
Keltezes DATE DEFAULT NULL,
aHetNapja TEXT
);
CREATE TABLE KivetelesNapokOraRendjei(
az INTEGER PRIMARY KEY UNIQUE,
Keltezes DATE DEFAULT NULL,
aHetNapja TEXT
);
There are datas already in this database.
The Orak table contains dates on which we have school, that is teaching.
That mean not every day in the year is a school day.
The OraRend table contains the timetable.
So when I start the android app on my phone it should display the
school day at that day, or, if on that day we have not a schoolday,
then to display the next school day from the Orak table.
Ah, good we checked.
What guarantee is there then that "tomorrow" will be a valid school day
if today isn't? What if today is Saturday, and we don't have school on
Saturday, then tomorrow is Sunday, which also might not be a school day?
To solve this puzzle, let's start by stating the problem in English:
"Give me the date for TODAY if TODAY is a valid school day, else give me
the date for the next possible valid school day."
Now let's translate that in terms of tables and records in a database:
"Give me the date for TODAY if any record exists in my table of
school-days for TODAY, else give me the first date (i.e. smallest date)
from a record that exists in the schooldays table AFTER (i.e.
later-than) today.
now let's say that in SQL:
SELECT MIN(TheDate) -- get the smallest date
FROM Orak -- from the table with School-days
WHERE TheDate >= date('now') -- where the school-day is later or
equal to today.
;
That should show the real date you need, in all cases, but assuming the
table has more available school days than today. (If nothing more
exists, nothing more can be shown).
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
