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

[R Smith]

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