Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[Csányi Pál]

2018-03-23 21:52 GMT+01:00 David Raymond :
> 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 

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[David Raymond]

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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

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
   query?



-
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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/21 9:58 PM, csanyipal wrote:
>>
>> I am really trying to understand how CTEs works and trying to achive my
>> goal
>> ( see bellow ) so I modified a little your code:
>> ...
>> As you can see I tried to add more CTEs into code out there but must
>> these
>> comment out because I get wrong Results.
>> So for now, with this code above I get followings ( for three school
>> weeks
>> ):
>>
>> 2017-09-01|F|1-2
>> 2017-09-01|F|3-4
>> 2017-09-01|F|5-6
>> 2017-09-04|M|1-2
>> 
>>
>> but I want followings ( for three school weeks ):
>>
>> 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
>> 
>>
>> So what SQL command should I use for this?
> 
> I'm not exactly sure how the classes work, it's hard to establish from 
> your explanation and SQL example output, for instance, in the output, 
> why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
> 5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
> only active on certain days? If so, we need a table to specify it so.
> 
> I like your table specifying the TimeTables. It needed fixing because 
> the VALUES mechanism works a bit differently than you think, but you can 
> compare between your and my version to see exactly how that works.
> Like I said, it's a bit unclear, but I'm going to assume some things and 
> suggest this:
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2017-09-01', '2017-09-21'
> ),DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'),
> (5,'F'),(6,'Sa')
> ),LBs(lessonBlock) 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'), ('37-38'), ('39-40'),
>     ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
> ('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
>     ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
> ('71-72')  */
> ), TimeTable(DoWeek,Grade,Class) AS (
>   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')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
>   UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate
> ), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
> TimeTable.Class
>    FROM CAL
>   CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>    JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
>   WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No
> Saturdays
> )
> SELECT *
>      FROM RES;
> 
> 
> I kept the majority of lesson blocks commented out for brevity, but if 
> it works, you can uncomment them and everything should work.
> 
> I hope it works, but if not, feel free to ask again, perhaps with a more 
> exact explanation of what the output should contain and how the lesson 
> blocks work.
> 
> 
> Cheers!
> Ryan

I tried it out, but it not gives the expected result.
I modified the 'Class' column name to 'ClassName' everywhere where it
occures, eg. like this:
TimeTable(DoWeek,Grade,ClassName)
and
TimeTable.ClassName

in order to make it more understandable.
Then I run it but it gives not the expected results.
This is because I was not clear enough about how the lesson blocks works.
But now this is what I'm supposed to do. My first language is not English,
so sorry if I fail in my attempt to explain this.
So in a school year on grade/class name ( like 5/a, 5/b, 5/c or 6/b, etc.)
must take 36 lesson blocks where a lesson block is built up of two lessons -
school hours - and lasts twice for 45 minutes. So in a school year there is
72 lessons, so 72/2=36 lesson blocks out there.
The lesson blocks I call like this: 1-2, 3-4, etc.
But there are school weeks too. In a school year there are 36 school weeks
out there.
In a school year there are 36 lesson blocks too. We numbering lesson blocks
in a school week, like this:
in the 1. school week (SW) the lesson block is called 1-2,
in the 2. SW 3-4,
...
in the 35. SW 69-70, and finally
in the 36. SW 71-72.

Now about the TimeTable.
On Friday 2017-09-01 I had one lesson block ( the 1-2 ) with 5/b Class and
one lesson block ( the 1-2 ) with 7/c Class.
On the next Friday I had one lesson block ( the 3-4 ) with 5/b Class and one
lesson block ( the 3-4 ) with 7/c Class.
And so on.
Say on Friday 2018-06-08 I will have one lesson block ( the 71-72 ) with 5/b
Class and one lesson block ( the 71-72 ) with 7/c Class.

So the records from the
SELECT *
 FROM RES;

should gives the followings ( see the Fridays how changes the 

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[R Smith]

On 2018/03/21 9:58 PM, csanyipal wrote:


I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:
...
As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2


but I want followings ( for three school weeks ):

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


So what SQL command should I use for this?


I'm not exactly sure how the classes work, it's hard to establish from 
your explanation and SQL example output, for instance, in the output, 
why does Friday 1 September 2017 only have lesson-block 1-2 for grades 
5b and 7c... why not lesson blocks 3-4 or 5-6? ARe the lesson blocks 
only active on certain days? If so, we need a table to specify it so.


I like your table specifying the TimeTables. It needed fixing because 
the VALUES mechanism works a bit differently than you think, but you can 
compare between your and my version to see exactly how that works.
Like I said, it's a bit unclear, but I'm going to assume some things and 
suggest this:


WITH PAR(calStartDate, calEndDate) AS (
    SELECT '2017-09-01', '2017-09-21'
),DoW(dayId,dayName) AS (
    VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),(4,'Th'), (5,'F'),(6,'Sa')
),LBs(lessonBlock) 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'), ('37-38'), ('39-40'),
   ('41-42'), ('43-44'), ('45-46'), ('47-48'), ('49-50'), 
('51-52'), ('53-54'), ('55-56'), ('57-58'), ('59-60'),
   ('61-62'), ('63-64'), ('65-66'), ('67-68'), ('69-70'), 
('71-72')  */

), TimeTable(DoWeek,Grade,Class) AS (
 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')
), CAL(dayDate,nextDay,dayId) AS (
    SELECT date(calStartDate,'-1 day'), date(calStartDate), -1 FROM PAR
 UNION ALL
    SELECT nextDay, date(nextDay,'+1 day'), CAST(STRFTIME('%w',nextDay) 
AS INT) FROM CAL,PAR WHERE nextDay <= calEndDate

), RES(dayDate, dayName, lessonBlock, Grade, Class) AS (
    SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock, TimeTable.Grade, 
TimeTable.Class

  FROM CAL
 CROSS JOIN LBs
  JOIN DoW ON DoW.dayID = CAL.dayId
  JOIN TimeTable ON TimeTable.DoWeek = DoW.dayName
 WHERE CAL.dayId > 0 AND CAL.dayId < 6  -- No Sundays and No Saturdays
)
SELECT *
    FROM RES;


I kept the majority of lesson blocks commented out for brevity, but if 
it works, you can uncomment them and everything should work.


I hope it works, but if not, feel free to ask again, perhaps with a more 
exact explanation of what the output should contain and how the lesson 
blocks work.



Cheers!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

David Raymond wrote
> In the commented out section:
> 
> TimeTable(DoWeek,Grade,Class_) AS
> (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')...
> 
> Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5,
> 'c')...?
> 
> WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
>  DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
> (4,'Th'), (5,'F'),(6,'Sa')),
>  LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
>   , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
> ('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
>   , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
>  /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
>  Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
>  TimeTable(DoWeek,Grade,Class_) AS (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')),*/
>  CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
>  date(calStartDate), -1
>  FROM PAR
>  UNION ALL
>   SELECT nextDay, date(nextDay,'+1 day'),
> CAST(STRFTIME('%w',nextDay) AS INT)
> FROM CAL,PAR
> WHERE nextDay <= calEndDate),
>  RES(dayDate, dayName, lessonBlock) AS
>(SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>   FROM CAL
>CROSS JOIN LBs
>JOIN DoW ON DoW.dayID = CAL.dayId
>WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
> Saturdays */
> SELECT *
> FROM RES;
> 

Indeed. I corrected that part and add the
JOIN TimeTable
like this:
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN TimeTable
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */

but get wrong outputs:
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2
2017-09-04|M|1-2

etc.
What am I doing wrong?



-
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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (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')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[David Raymond]

In the commented out section:

TimeTable(DoWeek,Grade,Class_) AS 
(VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')...

Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5, 'c')...?




-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of csanyipal
Sent: Wednesday, March 21, 2018 3:58 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Is it possible to CREATE TABLE from other tables in a 
complex way?

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (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')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> On 2018/03/17 12:40 PM, csanyipal wrote:
>> R Smith-2 wrote
>>> Here is a query that will produce all days of the year (without Sundays)
>>> plus their week days (and I've expanded for lesson blocks too, but you
>>> will probably need to add/edit as I don't know the exact values, but the
>>> method should be clear). You can JOIN this to the other tables
>>> containing courses and such to populate the hours table.
>>>
>>> WITH PAR(calStartDate, calEndDate) AS (
>>>       SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>>> ), DoW(dayId,dayName) AS (
>>>       VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
>>> (6,'Sa')
>>> ), LBs(lessonBlock) AS (
>>>       VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>>> ), CAL(dayDate,nextDay,dayId) AS (
>>>       SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM
>>> PAR
>>>       UNION ALL
>>>       SELECT nextDay, date(nextDay,'+1 day'),
>>> CAST(strftime('%w',nextDay)
>>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>>> ), RES(dayDate, dayName, lessonBlock) AS (
>>>       SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>>     FROM CAL
>>>     CROSS JOIN LBs
>>>     JOIN DoW ON DoW.dayID = CAL.dayId
>>>    WHERE CAL.dayId > 0  -- No Sundays
>>> )
>>> SELECT *
>>>     FROM RES
>> PAR and RES are table names; please tell me what is the meaning of the
>> PAR
>> and RES abbreviations?
>> This is what I need to better understand this query.
> 
> PAR and RES are simply names for the common table expression (CTE) views 
> I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
> supply Start-Date and End-Date parameters in that first PAR view - it 
> has no other use.
> 
> I chose RES as short for "Results" and CAL as short for Calendar.
> 
> In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
> followed by a "SELECT FROM itself" shows it is recursive) to make up all 
> the dates cross-joined by PAR so I can limit it to go no further than 
> calEndDate. You can achieve the same by simply hard-coding the dates in 
> CAL (in stead of joining the PAR view), but I tend to find it more 
> sensible to put "things that might change" right at the top of the query 
> mimicking the parameters of normal programming - That's all the PAR is 
> for, it's not in any way mandatory.
> 
> In the RES CTE view, I simply join all the dates from the recursive cte 
> calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
> (LB) to produce the resulting output we wanted.
> 
> One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
> simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
> one of the CTE views used so that you can debug/inspect it to understand 
> what they do.
> 
> I hope that clears it up, but please feel free to ask more if you have 
> more questions - understanding CTEs well is a great advantage when using 
> sql.
> 
> 
> Cheers,
> Ryan

Thank you Ryan, for the explanations!

I am really trying to understand how CTEs works and trying to achive my goal
( see bellow ) so I modified a little your code:

WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09-01', '2017-09-21'),
 DoW(dayId,dayName) AS (VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'),
(4,'Th'), (5,'F'),(6,'Sa')),
 LBs(lessonBlock) 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'), ('37-38'), ('39-40'), ('41-42')
  , ('43-44'), ('45-46'), ('47-48'), ('49-50'), ('51-52'), ('53-54'),
('55-56'), ('57-58'), ('59-60'), ('61-62'), ('63-64')
  , ('65-66'), ('67-68'), ('69-70'), ('71-72')*/),
 /*Grades(Grade) AS (VALUES (5), (6), (7), (8)),
 Classes(Class) AS (VALUES ('a'), ('b'), ('c')),
 TimeTable(DoWeek,Grade,Class_) AS (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')),*/
 CAL(dayDate,nextDay,dayId) AS (SELECT date(calStartDate,'-1 day'),
 date(calStartDate), -1
 FROM PAR
 UNION ALL
  SELECT nextDay, date(nextDay,'+1 day'),
CAST(STRFTIME('%w',nextDay) AS INT)
FROM CAL,PAR
WHERE nextDay <= calEndDate),
 RES(dayDate, dayName, lessonBlock) AS
 (SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
   CROSS JOIN LBs
   JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0 AND CAL.dayId < 6)  /* No Sundays and No
Saturdays */
SELECT *
FROM RES;

As you can see I tried to add more CTEs into code out there but must these
comment out because I get wrong Results.
So for now, with this code above I get followings ( for three school weeks
):

2017-09-01|F|1-2
2017-09-01|F|3-4
2017-09-01|F|5-6
2017-09-04|M|1-2
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[R Smith]

On 2018/03/17 12:40 PM, csanyipal wrote:

R Smith-2 wrote

Here is a query that will produce all days of the year (without Sundays)
plus their week days (and I've expanded for lesson blocks too, but you
will probably need to add/edit as I don't know the exact values, but the
method should be clear). You can JOIN this to the other tables
containing courses and such to populate the hours table.

WITH PAR(calStartDate, calEndDate) AS (
      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
), DoW(dayId,dayName) AS (
      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'),
(6,'Sa')
), LBs(lessonBlock) AS (
      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
), CAL(dayDate,nextDay,dayId) AS (
      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
      UNION ALL
      SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay)
AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
), RES(dayDate, dayName, lessonBlock) AS (
      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
    FROM CAL
    CROSS JOIN LBs
    JOIN DoW ON DoW.dayID = CAL.dayId
   WHERE CAL.dayId > 0  -- No Sundays
)
SELECT *
    FROM RES

PAR and RES are table names; please tell me what is the meaning of the PAR
and RES abbreviations?
This is what I need to better understand this query.


PAR and RES are simply names for the common table expression (CTE) views 
I chose arbitrarily, I took PAR to mean "Parameters" since I only really 
supply Start-Date and End-Date parameters in that first PAR view - it 
has no other use.


I chose RES as short for "Results" and CAL as short for Calendar.

In the Calendar cte (CAL) I simply use recursive cte (the "UNION ALL" 
followed by a "SELECT FROM itself" shows it is recursive) to make up all 
the dates cross-joined by PAR so I can limit it to go no further than 
calEndDate. You can achieve the same by simply hard-coding the dates in 
CAL (in stead of joining the PAR view), but I tend to find it more 
sensible to put "things that might change" right at the top of the query 
mimicking the parameters of normal programming - That's all the PAR is 
for, it's not in any way mandatory.


In the RES CTE view, I simply join all the dates from the recursive cte 
calendar (CAL) with the Day-of-Week cte (DoW) and the LessonBlocks cte 
(LB) to produce the resulting output we wanted.


One trick when using CTEs - The very bottom "SELECT FROM RES" you can 
simply change to be "SELECT FROM CAL" or "SELECT FROM DoW" or indeed any 
one of the CTE views used so that you can debug/inspect it to understand 
what they do.


I hope that clears it up, but please feel free to ask more if you have 
more questions - understanding CTEs well is a great advantage when using 
sql.



Cheers,
Ryan


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[Keith Medcalf]

PAR is Parameters (calendar start and end)
RES is Results

from what I see anyway ...


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of csanyipal
>Sent: Saturday, 17 March, 2018 04:40
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] Is it possible to CREATE TABLE from other
>tables in a complex way?
>
>R Smith-2 wrote
>> Here is a query that will produce all days of the year (without
>Sundays)
>> plus their week days (and I've expanded for lesson blocks too, but
>you
>> will probably need to add/edit as I don't know the exact values,
>but the
>> method should be clear). You can JOIN this to the other tables
>> containing courses and such to populate the hours table.
>>
>> WITH PAR(calStartDate, calEndDate) AS (
>>      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
>> ), DoW(dayId,dayName) AS (
>>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'),
>(5,'F'),
>> (6,'Sa')
>> ), LBs(lessonBlock) AS (
>>      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
>> ), CAL(dayDate,nextDay,dayId) AS (
>>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -
>1  FROM PAR
>>      UNION ALL
>>      SELECT nextDay, date(nextDay,'+1 day'),
>CAST(strftime('%w',nextDay)
>> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
>> ), RES(dayDate, dayName, lessonBlock) AS (
>>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>>    FROM CAL
>>    CROSS JOIN LBs
>>    JOIN DoW ON DoW.dayID = CAL.dayId
>>   WHERE CAL.dayId > 0  -- No Sundays
>> )
>> SELECT *
>>    FROM RES
>
>PAR and RES are table names; please tell me what is the meaning of
>the PAR
>and RES abbreviations?
>This is what I need to better understand this query.
>
>
>
>-
>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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.
> 
> WITH PAR(calStartDate, calEndDate) AS (
>      SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
> ), DoW(dayId,dayName) AS (
>      VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'), 
> (6,'Sa')
> ), LBs(lessonBlock) AS (
>      VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
> ), CAL(dayDate,nextDay,dayId) AS (
>      SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
>      UNION ALL
>      SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay) 
> AS INT) FROM CAL,PAR WHERE nextDay < calEndDate
> ), RES(dayDate, dayName, lessonBlock) AS (
>      SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
>    FROM CAL
>    CROSS JOIN LBs
>    JOIN DoW ON DoW.dayID = CAL.dayId
>   WHERE CAL.dayId > 0  -- No Sundays
> )
> SELECT *
>    FROM RES

PAR and RES are table names; please tell me what is the meaning of the PAR
and RES abbreviations?
This is what I need to better understand this query.



-
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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[csanyipal]

R Smith-2 wrote
> This seems like a whole assignment, and we are not in the habit to do 
> assignments for people,
> 
> BUT, we can get you started down the path.
> 
> You should know how to do all you are asking by simple RDBMS mechanics, 
> except maybe how to initialize a table with all dates and other things 
> pre-populated, so to assist with that
> 
> Here is a query that will produce all days of the year (without Sundays) 
> plus their week days (and I've expanded for lesson blocks too, but you 
> will probably need to add/edit as I don't know the exact values, but the 
> method should be clear). You can JOIN this to the other tables 
> containing courses and such to populate the hours table.

I did not even think that you would do the job for me.
However, thank you very much for the path you showed.
That's what I wanted to, to get me started down the path.
Thank you very much!



-
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

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[Simon Slavin]

On 11 Mar 2018, at 12:05pm, Csányi Pál  wrote:

> Columns are: id,date,D,lb,g,c,lp,ld,re
> 
> where D is a Day name in Week,
> lb is the number of the Lesson Block hour,
> g is the grade of a school class,
> c is the name of the school class,
> lp is LessonPlan,
> ld is LessonDiary,
> re is Reminder.

Don't do that.  If a column contains a lesson plan, call it "lessonPlan".  
Don't make up a short form you will forget.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[R Smith]

This seems like a whole assignment, and we are not in the habit to do 
assignments for people,


BUT, we can get you started down the path.

You should know how to do all you are asking by simple RDBMS mechanics, 
except maybe how to initialize a table with all dates and other things 
pre-populated, so to assist with that


Here is a query that will produce all days of the year (without Sundays) 
plus their week days (and I've expanded for lesson blocks too, but you 
will probably need to add/edit as I don't know the exact values, but the 
method should be clear). You can JOIN this to the other tables 
containing courses and such to populate the hours table.


WITH PAR(calStartDate, calEndDate) AS (
    SELECT '2018-01-01 00:00:00', '2018-12-31 23:59:59'
), DoW(dayId,dayName) AS (
    VALUES (0,'Su'), (1,'M'), (2,'Tu'), (3,'W'), (4,'Th'), (5,'F'), 
(6,'Sa')

), LBs(lessonBlock) AS (
    VALUES ('1-2'), ('2-3'), ('3-4'), ('4-5')
), CAL(dayDate,nextDay,dayId) AS (
    SELECT date(calStartDate,'-1 day'), date(calStartDate), -1  FROM PAR
    UNION ALL
    SELECT nextDay, date(nextDay,'+1 day'), CAST(strftime('%w',nextDay) 
AS INT) FROM CAL,PAR WHERE nextDay < calEndDate

), RES(dayDate, dayName, lessonBlock) AS (
    SELECT CAL.dayDate, DoW.dayName, LBs.lessonBlock
  FROM CAL
  CROSS JOIN LBs
  JOIN DoW ON DoW.dayID = CAL.dayId
 WHERE CAL.dayId > 0  -- No Sundays
)
SELECT *
  FROM RES


PS: There are efficiency improvements possible, but this is more to show 
the method than to be 100% processor friendly.



On 2018/03/11 2:05 PM, Csányi Pál wrote:

Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

[Csányi Pál]

Hi,

is it possible to create a table from other tables with the following
conditions?

Note!
My goal is to create that table 'Hours' with a say 362 records and
after that to update those records with UPDATE sql commands. That is,
I don't want to INSERT in that table any more records, but just UPDATE
it's existing records only with new or modified informations.

The conditions are as follows.
The resulting table called Hours should look like this:

Columns are: id,date,D,lb,g,c,lp,ld,re

where D is a Day name in Week,
lb is the number of the Lesson Block hour,
g is the grade of a school class,
c is the name of the school class,
lp is LessonPlan,
ld is LessonDiary,
re is Reminder.

Example records would be like this:

1,2017-9-1,F,1-2,5,b,,,
2,2017-9-1,F,1-2,7,c,,,
3,2017-9-4,M,1-2,7,b,,,
4,2017-9-4,M,1-2,5,a,,,
5,2017-9-5,Tu,1-2,8,c,,,
6,2017-9-5,Tu,1-2,8,b,,,
7,2017-9-6,W,1-2,8,a,,,
8,2017-9-6,W,1-2,7,a,,,
9,2017-9-7,Th,1-2,6,a,,,
10,2017-9-7,Th,1-2,5,c,,,
11,2017-9-8,F,3-4,5,b,,,
12,2017-9-8,F,3-4,7,c,,,

where the last three columns have NULL values so far, because later I
want to fill those with informations by using UPDATE sql command.

So in Hours table one or more records could have the same date, Day
name in Week, Lesson Block number, but could have also same grade, and
same class name. Only id is UNIQUE in these records.

For start I will be happy to get the Hours table with proper date and
Day name in Week values out there.

Which date is proper in the Hours table should be determined by
another table in the database. Let this table be called the
'Semesters'.

However, a date value in Hours table should not be a Sunday date in the Year.

The Semesters table should have the following columns:
id,numberOfSemester, StartDay,EndDay

In a record of Hours table a date should have the Day name in the Week
which it has in the Calendar, but this behavior can be changed by
another two tables in the database; let these be called the
'TeachingSaturdays' and 'Timetable Exceptions'.

The TeachingSaturdays table should have the following columns:
id,date,D

where the value for date column  is definitely a Saturday day date,
and D could be one of these: M, Tu, W, Th, F.

The TimetableExceptions table should have the following columns:
id,date,D

where the value for D column could be one of these: M, Tu, W, Th, F.

Well, I hope I have clearly defined the problem.

Can this Hours table be created with these constraints?

-- 
Best, Pali
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users