Number lessons in a schedule

[HaraldS]

Hello world,

I'm creating a database in a music teaching environment. It creates a schedule which can be saved as an iCalendar .ics file. I already got this working, but I'm struggling in extending my SQL statement to number the lessons.

I have two tables:
- "Stammdaten", which contain all students and their weekly time which they booked (e.g. Monday, 14:00-14:45, always one student per time)
- "Kalender", containing every day of the years 2024/2025/2026 and its boolean status whether it's a school day (lessons happens only outside school holidays and bank holidays)

I get my schedule via this working SQL statement:
SELECT "Kalender"."ID", "Stammdaten"."Nachname", "Stammdaten"."Vorname", "Kalender"."Datum", "Stammdaten"."Unterrichszeitbeginn", "Stammdaten"."Unterrichtsdauer", "Stammdaten"."Fach", "Stammdaten"."Wochen", "Stammdaten"."Anzahl Stunden"
FROM "Kalender" AS "Kalender"
CROSS JOIN "Stammdaten" AS "Stammdaten"
WHERE "Kalender"."Unterrichtstag" = TRUE AND ( "Kalender"."Datum" >= "Stammdaten"."Belegungsbeginn" ) AND ( "Kalender"."Datum" <= "Stammdaten"."Belegungsende" ) AND "Stammdaten"."Unterrichtswochentag" = DAYOFWEEK( "Kalender"."Datum" )
AND ( ( "Stammdaten"."Wochen" = 'Jede' ) OR ( "Stammdaten"."Wochen" = 'Gerade' AND MOD( WEEK( "Kalender"."Datum" ), 2 ) = 0 ) OR ( "Stammdaten"."Wochen" = 'Ungerade' AND MOD( WEEK( "Kalender"."Datum" ), 2 ) = 1 ) )

This gives me a schedule showing each lesson in the future. Now I want to number the lessons: the query should add a column where the student's first lessons gets a 1, the second a 2 and so on.

I saw this thread viewtopic.php?t=99451 and tried to adapt the method, but it gives no strict incremental numbering when there are gaps in the original data.

Any hints would be much appreciated.

Harald

[chrisb]

the table "Stammdaten" looks like a spreadsheet dump.
students & subjects should be stored in separate independent tables.
tables should be linked using primary to foreign key.

a simple example:

tLessons
ID int autoval
Date
StartTime
Duration (minutes)
StudentID references "tStudents"."ID"
SubjectID references "tSubjects"."ID"

tStudents
ID int autoval
Fname
Lname

tSubjects
ID int autoval
Name

sql to calculate lesson number:

Code: Select all

select
	st."Fname" || ', ' || st."Lname" "Student", t1."Date",
	t1."StartTime", t1."Duration", su."Name", t2."LessonNum"
from
	"tLessons" t1
join
	(
		select a."Date", a."StudentID", count(b."StudentID") "LessonNum"
		from
		(select "Date", "StudentID" from "tLessons") a
		join
		(select "Date", "StudentID" from "tLessons") b
		on a."StudentID" = b."StudentID" and a."Date" >= b."Date"
		group by a."StudentID", a."Date"
	) t2
	on t1."StudentID" = t2."StudentID" and t1."Date" = t2."Date"
join
	"tStudents" st
	on t1."StudentID" = st.ID
join
	"tSubjects" su
	on t1."SubjectID" = su.ID
order by "Student", "LessonNum"

if you wish to view the query results then download the hsql embedded attachment.

[HaraldS]

Thanks, I‘ll have a look into that!