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