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