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.
-----
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
