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
