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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by csanyipal
On 11 Mar 2018, at 12:05pm, Csányi Pál <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by R Smith-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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-
>[hidden email]] On Behalf Of csanyipal
>Sent: Saturday, 17 March, 2018 04:40
>To: [hidden email]
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by csanyipal


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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:
<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;
</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
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4
2017-09-05|Tu|5-6
2017-09-06|W|1-2
2017-09-06|W|3-4
2017-09-06|W|5-6
2017-09-07|Th|1-2
2017-09-07|Th|3-4
2017-09-07|Th|5-6
2017-09-08|F|1-2
2017-09-08|F|3-4
2017-09-08|F|5-6
2017-09-11|M|1-2
2017-09-11|M|3-4
2017-09-11|M|5-6
2017-09-12|Tu|1-2
2017-09-12|Tu|3-4
2017-09-12|Tu|5-6
2017-09-13|W|1-2
2017-09-13|W|3-4
2017-09-13|W|5-6
2017-09-14|Th|1-2
2017-09-14|Th|3-4
2017-09-14|Th|5-6
2017-09-15|F|1-2
2017-09-15|F|3-4
2017-09-15|F|5-6
2017-09-18|M|1-2
2017-09-18|M|3-4
2017-09-18|M|5-6
2017-09-19|Tu|1-2
2017-09-19|Tu|3-4
2017-09-19|Tu|5-6
2017-09-20|W|1-2
2017-09-20|W|3-4
2017-09-20|W|5-6
2017-09-21|Th|1-2
2017-09-21|Th|3-4
2017-09-21|Th|5-6

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

So what SQL command should I use for this?



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] On Behalf Of csanyipal
Sent: Wednesday, March 21, 2018 3:58 PM
To: [hidden email]
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:
<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;
</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
2017-09-04|M|3-4
2017-09-04|M|5-6
2017-09-05|Tu|1-2
2017-09-05|Tu|3-4
2017-09-05|Tu|5-6
2017-09-06|W|1-2
2017-09-06|W|3-4
2017-09-06|W|5-6
2017-09-07|Th|1-2
2017-09-07|Th|3-4
2017-09-07|Th|5-6
2017-09-08|F|1-2
2017-09-08|F|3-4
2017-09-08|F|5-6
2017-09-11|M|1-2
2017-09-11|M|3-4
2017-09-11|M|5-6
2017-09-12|Tu|1-2
2017-09-12|Tu|3-4
2017-09-12|Tu|5-6
2017-09-13|W|1-2
2017-09-13|W|3-4
2017-09-13|W|5-6
2017-09-14|Th|1-2
2017-09-14|Th|3-4
2017-09-14|Th|5-6
2017-09-15|F|1-2
2017-09-15|F|3-4
2017-09-15|F|5-6
2017-09-18|M|1-2
2017-09-18|M|3-4
2017-09-18|M|5-6
2017-09-19|Tu|1-2
2017-09-19|Tu|3-4
2017-09-19|Tu|5-6
2017-09-20|W|1-2
2017-09-20|W|3-4
2017-09-20|W|5-6
2017-09-21|Th|1-2
2017-09-21|Th|3-4
2017-09-21|Th|5-6

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

So what SQL command should I use for this?



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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')...?
> <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;
> </code>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by csanyipal


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 lesson blocks
):

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

I hope that that this is clear now, right?
So how can I get this output?



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
This post was updated on .
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?



-----
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
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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
> &lt;http://www.sqlite.org/lang_select.html&gt;   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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

Re: 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:[hidden email]] On Behalf Of csanyipal
Sent: Friday, March 23, 2018 4:19 PM
To: [hidden email]
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
> &lt;http://www.sqlite.org/lang_select.html&gt;   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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
2018-03-23 21:52 GMT+01:00 David Raymond <[hidden email]>:
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
In reply to this post by David Raymond


>-----Ursprüngliche Nachricht-----
>Von: sqlite-users [mailto:[hidden email]] Im Auftrag von David Raymond
>Gesendet: Freitag, 23. März 2018 21:52
>An: SQLite mailing list <[hidden email]>
>Betreff: [EXTERNAL] Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?
>
>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)

Please note that %G/%g (ISO year) goes with %V (ISO week number) and %Y/%y (US Year) goes with %W (US week number). Mixing standards will give you some very wierd (and invalid) results when handling the week around New Year.


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users