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