2018-03-23 21:52 GMT+01:00 David Raymond <david.raym...@tomtom.com>:
> This gets a little ugly. Was stuck for a while wondering what the heck was
> going on until I found out that the modulo operator can return negatives.
> Which makes absolutely no sense coming from someone who was a math major, but
> hey, now I know. It's also fun that %w "day of week" goes Sunday to Sunday,
> but %W "week of year" goes Monday to Monday (And then your school weeks go
> Friday to Friday)
>
> Tables and fields have been renamed slightly from the previous emails. The 4
> tables I'm putting here are all static, so you can put them at the front of
> your CTE with values clauses if you really, really want to make it one big
> select statement from no tables.
Thank you very much.
I was already thinking of using static tables.
I modify your code a little: we call school years like this:
'2016/2017', '2017/2018' and so on.
Then I run it and then I realized there were breaks and holidays in a
school year.
Those days of holidays and breaks should be excluded from the result records.
Moreover, there are teaching Saturdays too, when on a Saturday we must
to take Lessons. Eg. on 2018-04-14 we must go to school and take
Lessons according to the Friday timetable. These teaching Saturdays
should be added to the result with UNION clause.
So I created more tables and now I have the Lessons.db with these clauses:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE SchoolYearDates
(
SchoolYear text NOT NULL PRIMARY KEY COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO SchoolYearDates VALUES('2017/2018','2017-09-01','2018-06-14');
CREATE TABLE TeachingSaturdays
(
SaturdayDate text NOT NULL COLLATE nocase
CHECK (date(SaturdayDate) IS NOT NULL ),
TimeTableDay text NOT NULL COLLATE nocase
);
INSERT INTO TeachingSaturdays VALUES ('2018-04-14','F');
INSERT INTO TeachingSaturdays VALUES ('2018-05-05','M');
CREATE TABLE SchoolVacations
(
VacationName text NOT NULL COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO SchoolVacations VALUES ('Winter Break','2017-12-25','2018-01-09');
INSERT INTO SchoolVacations VALUES ('Spring Break','2018-03-30','2018-04-09');
INSERT INTO SchoolVacations VALUES ('Summer Break','2018-06-15','2018-08-31');
CREATE TABLE HoliDays
(
HoliDayName text NOT NULL COLLATE nocase,
StartDate text NOT NULL COLLATE nocase
CHECK (date(StartDate) IS NOT NULL ),
EndDate text NOT NULL COLLATE nocase
CHECK (date(EndDate) IS NOT NULL )
);
INSERT INTO HoliDays VALUES ('Name 1','2017-10-21','2017-10-21');
INSERT INTO HoliDays VALUES ('Name 2','2017-11-11','2017-11-11');
INSERT INTO HoliDays VALUES ('Name 4','2018-02-15','2018-02-16');
INSERT INTO HoliDays VALUES ('Name 3','2018-02-27','2018-02-27');
INSERT INTO HoliDays VALUES ('Name 5','2018-04-22','2018-04-22');
INSERT INTO HoliDays VALUES ('Name 6','2018-05-01','2018-05-02');
CREATE TABLE DaysOfWeek
(
DayID integer NOT NULL PRIMARY KEY ,
DayAbbrev text NOT NULL COLLATE nocase,
DayName text NOT NULL COLLATE nocase
);
INSERT INTO DaysOfWeek VALUES(0,'Su','Sunday');
INSERT INTO DaysOfWeek VALUES(1,'M','Monday');
INSERT INTO DaysOfWeek VALUES(2,'Tu','Tuesday');
INSERT INTO DaysOfWeek VALUES(3,'W','Wednesday');
INSERT INTO DaysOfWeek VALUES(4,'Th','Thursday');
INSERT INTO DaysOfWeek VALUES(5,'F','Friday');
INSERT INTO DaysOfWeek VALUES(6,'Sa','Saturday');
CREATE TABLE LessonBlocks
(
WeekNumber integer NOT NULL PRIMARY KEY ,
LessonBlock text NOT NULL COLLATE nocase
);
INSERT INTO LessonBlocks VALUES(1,'1-2');
INSERT INTO LessonBlocks VALUES(2,'3-4');
INSERT INTO LessonBlocks VALUES(3,'5-6');
INSERT INTO LessonBlocks VALUES(4,'7-8');
INSERT INTO LessonBlocks VALUES(5,'9-10');
INSERT INTO LessonBlocks VALUES(6,'11-12');
INSERT INTO LessonBlocks VALUES(7,'13-14');
INSERT INTO LessonBlocks VALUES(8,'15-16');
INSERT INTO LessonBlocks VALUES(9,'17-18');
INSERT INTO LessonBlocks VALUES(10,'19-20');
INSERT INTO LessonBlocks VALUES(11,'21-22');
INSERT INTO LessonBlocks VALUES(12,'23-24');
INSERT INTO LessonBlocks VALUES(13,'25-26');
INSERT INTO LessonBlocks VALUES(14,'27-28');
INSERT INTO LessonBlocks VALUES(15,'29-30');
INSERT INTO LessonBlocks VALUES(16,'31-32');
INSERT INTO LessonBlocks VALUES(17,'33-34');
INSERT INTO LessonBlocks VALUES(18,'35-36');
INSERT INTO LessonBlocks VALUES(19,'37-38');
INSERT INTO LessonBlocks VALUES(20,'39-40');
INSERT INTO LessonBlocks VALUES(21,'41-42');
INSERT INTO LessonBlocks VALUES(22,'43-44');
INSERT INTO LessonBlocks VALUES(23,'45-46');
INSERT INTO LessonBlocks VALUES(24,'47-48');
INSERT INTO LessonBlocks VALUES(25,'49-50');
INSERT INTO LessonBlocks VALUES(26,'51-52');
INSERT INTO LessonBlocks VALUES(27,'53-54');
INSERT INTO LessonBlocks VALUES(28,'55-56');
INSERT INTO LessonBlocks VALUES(29,'57-58');
INSERT INTO LessonBlocks VALUES(30,'59-60');
INSERT INTO LessonBlocks VALUES(31,'61-62');
INSERT INTO LessonBlocks VALUES(32,'63-64');
INSERT INTO LessonBlocks VALUES(33,'65-66');
INSERT INTO LessonBlocks VALUES(34,'67-68');
INSERT INTO LessonBlocks VALUES(35,'69-70');
INSERT INTO LessonBlocks VALUES(36,'71-72');
CREATE TABLE TimeTable
(
DayOfWeek text NOT NULL COLLATE nocase,
Grade int NOT NULL ,
Class text NOT NULL COLLATE nocase
);
INSERT INTO TimeTable VALUES('M',7,'b');
INSERT INTO TimeTable VALUES('M',5,'a');
INSERT INTO TimeTable VALUES('Tu',8,'c');
INSERT INTO TimeTable VALUES('Tu',8,'b');
INSERT INTO TimeTable VALUES('W',8,'a');
INSERT INTO TimeTable VALUES('W',7,'a');
INSERT INTO TimeTable VALUES('Th',6,'a');
INSERT INTO TimeTable VALUES('Th',5,'c');
INSERT INTO TimeTable VALUES('F',5,'b');
INSERT INTO TimeTable VALUES('F',7,'c');
COMMIT;
Now the question is how to exclude those days determined by the
TeachingSaturdays, SchoolVacations, HoliDays tables from the resulting
records? How to modify the WITH RECURSIVE clause:
WITH RECURSIVE
Days (DayDate, DayOfWeek, WeekNumber) AS (
SELECT
StartDate,
(SELECT DayAbbrev FROM DaysOfWeek
WHERE DayID = CAST (strftime('%w', StartDate) AS int)),
1
FROM SchoolYearDates
WHERE SchoolYear = '2017/2018'
UNION ALL
SELECT
date(DayDate, '+1 day'),
(SELECT DayAbbrev FROM DaysOfWeek
WHERE DayID = CAST (strftime('%w', DayDate, '+1 day') AS int)),
(
(
CAST (strftime('%W', DayDate, '+1 day') AS int)
- CAST (strftime('%W', (SELECT StartDate FROM SchoolYearDates
WHERE SchoolYear = '2017/2018')) AS int)
+ ((CAST (strftime('%w', DayDate, '+1 day') AS int) + 6) % 7 >= 4)
) + 52
) % 52
FROM Days
WHERE DayDate <= (SELECT EndDate FROM SchoolYearDates
WHERE SchoolYear = '2017/2018')
)
SELECT
Days.DayDate,
Days.DayOfWeek,
LessonBlocks.LessonBlock,
TimeTable.Grade,
TimeTable.Class
FROM
Days INNER JOIN LessonBlocks
ON Days.WeekNumber = LessonBlocks.WeekNumber
INNER JOIN TimeTable
ON Days.DayOfWeek = TimeTable.DayOfWeek
ORDER BY
DayDate;
--
Best, Pali
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
