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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
