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.
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', '2017-09-01', '2018-06-08');
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'),
(1, 'M', 'Monday'),
(2, 'Tu', 'Tuesday'),
(3, 'W', 'Wednesday'),
(4, 'Th', 'Thursday'),
(5, 'F', 'Friday'),
(6, 'Sa', 'Saturday');
CREATE TABLE LessonBlocks
(
WeekNumber integer not null primary key,
LessonBlock text not null collate nocase
);
with recursive foo (WeekNumber, LessonBlock) as (
values (1, '1-2')
union all
select
WeekNumber + 1,
cast(WeekNumber * 2 + 1 as text) || '-' || cast(WeekNumber * 2 + 2 as text)
from foo
where WeekNumber < 36)
insert into LessonBlocks select * from foo;
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'), ('M', 5, 'a'),
('Tu', 8, 'c'), ('Tu', 8, 'b'),
('W', 8, 'a'), ('W', 7, 'a'),
('Th', 6, 'a'), ('Th', 5, 'c'),
('F', 5, 'b'), ('F', 7, 'c');
--Now the fun part (Trying to limit to 80 chars per line will be weird
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'
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')) 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')
)
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, Grade, Class;
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of csanyipal
Sent: Friday, March 23, 2018 4:19 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Is it possible to CREATE TABLE from other tables in a
complex way?
csanyipal wrote
> csanyipal wrote
> 2017-09-01|F|1-2|5|b
> 2017-09-01|F|1-2|7|c
> 2017-09-04|M|1-2|7|b
> 2017-09-04|M|1-2|5|a
> 2017-09-05|Tu|1-2|8|c
> 2017-09-05|Tu|1-2|8|b
> 2017-09-06|W|1-2|8|a
> 2017-09-06|W|1-2|7|a
> 2017-09-07|Th|1-2|6|a
> 2017-09-07|Th|1-2|5|c
> 2017-09-08|F|3-4|5|b
> 2017-09-08|F|3-4|7|c
> 2017-09-11|M|3-4|7|b
> 2017-09-11|M|3-4|5|a
> 2017-09-12|Tu|3-4|8|c
> 2017-09-12|Tu|3-4|8|b
> 2017-09-13|W|3-4|8|a
> 2017-09-13|W|3-4|7|a
> 2017-09-14|Th|3-4|6|a
> 2017-09-14|Th|3-4|5|c
> 2017-09-15|F|5-6|5|b
> 2017-09-15|F|5-6|7|c
> 2017-09-18|M|5-6|7|b
> 2017-09-18|M|5-6|5|a
> 2017-09-19|Tu|5-6|8|c
> 2017-09-19|Tu|5-6|8|b
> 2017-09-20|W|5-6|8|a
> 2017-09-20|W|5-6|7|a
> 2017-09-21|Th|5-6|6|a
> 2017-09-21|Th|5-6|5|c
>
> Can this output be achieved with a SELECT
> <http://www.sqlite.org/lang_select.html> query?
I think I must to create one more CTE called SchoolWeeks and must modify the
LBs table like this:
LBs(SchoolWeek,lessonBlock) AS (
VALUES (1,'1-2'), (2,'3-4')/*, (3,'5-6'), (4,'7-8'),(5,'9-10'),
(6,'11-12'),
(7,'13-14'), (8,'15-16'), (9,'17-18'), (10,'19-20'),
(11,'21-22'), (12,'23-24'), (13,'25-26'), (14,'27-28'),
(15,'29-30'),
(16,'31-32'), (17,'33-34'), (18,'35-36'), (19,'37-38'), (20,'39-40'),
(21,'41-42'), (22,'43-44'), (23,'45-46'), (24,'47-48'),
(25,'49-50'),
(26,'51-52'), (27,'53-54'), (28,'55-56'), (29,'57-58'), (30,'59-60'),
(31,'61-62'), (32,'63-64'), (33,'65-66'), (34,'67-68'),
(35,'69-70'),
(36,'71-72') */
SWs(SchoolWeek) AS VALUES
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36)
so I can JOIN these in a proper way. Right?
-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
