Page 1 of 4 1234 LastLast
Results 1 to 40 of 131

Thread: Another complicated query but perhaps possible

Hybrid View

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Another complicated query but perhaps possible

    Hello evryone
    First of all I have to thank jdelano and Zvoni who have helped me in my last thread.
    In fact everything is OK except I was met with a case wher a teacher is assigned two different subjects.
    Here is the case:

    Code:
    Set Cnn = New_c.Connection(, DBCreateInMemory)
    With Cnn.NewFieldDefs
     .Add "St_ID   Integer "
     .Add "T_ID   Integer "
     .Add "Sfees   Double"
     .Add "subj_ID   Integer"
    End With
    Cnn.CreateTable " Tbl1"
    Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (1,1,40, 1)"
    Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (2,1,30, 1)"
    Cnn.Execute "INSERT INTO Tbl1 (St_ID, T_ID, Sfees, subj_ID) VALUES (3,1,40, 2)"
    Code:
    With Cnn.NewFieldDefs
    .Add "T_ID   Integer "
    .Add "N_Trans   Integer"
    .Add "TFees   Double"
    End With
    Cnn.CreateTable " Tbl2"
    Cnn.Execute "INSERT INTO Tbl2 ( T_ID,  N_Trans, TFees) VALUES ( 1, 1, 10)"
    Cnn.Execute "INSERT INTO Tbl2 ( T_ID , N_Trans, TFees) VALUES ( 1, 1, 10)"
    Cnn.Execute "INSERT INTO Tbl2 ( T_ID , N_Trans, TFees) VALUES ( 1, 2, 10)"
    Code:
    With Cnn.NewFieldDefs
     .Add "T_ID   Integer "
     .Add "N_Trans   Integer"
     .Add "subj_ID   Integer"
    End With
    Cnn.CreateTable "Tbl3"
    Cnn.Execute "INSERT INTO Tbl3 ( T_ID,  N_Trans, subj_ID) VALUES ( 1, 1, 1)"
    Cnn.Execute "INSERT INTO Tbl3 ( T_ID , N_Trans, subj_ID) VALUES ( 1, 2, 2)"
    Code:
    With Cnn.NewFieldDefs
     .Add "ID   Integer Primary key"
     .Add "Subj   Text"
     End With
    Cnn.CreateTable "Subj_tbl"
    
    Cnn.Execute "INSERT INTO Subj_tbl (ID, Subj) VALUES ('1', 'Phys')"
    Cnn.Execute "INSERT INTO Subj_tbl (ID, Subj) VALUES ('2', 'Maths')"
    Code:
    StrSql = "StrSql = "SELECT T2.N_Trans, Subj, T1.Sfees * 50 / 100 as Totalprice , T3.N_Trans, T2.TFees, Subj_tbl.ID, T2.T_ID, (T1.Totalprice *  50  /100)-SUM(T2.TFees) OVER(PARTITION BY T2.N_Trans,T3.subj_ID " & _
    " ORDER BY T2.ROWID) As RestToPay " & _
    " FROM (SELECT subj_ID, T_ID, subj_ID, Sfees, SUM(Sfees) AS Totalprice FROM Tbl1 Where T_ID = 1   GROUP BY T_ID,subj_ID ) AS T1 " & _
    "left join Subj_tbl on T1.subj_ID = Subj_tbl.ID " & _
    " INNER JOIN Tbl2 AS T2 ON T1.T_ID=T2.T_ID " & _
    " INNER JOIN Tbl3 AS T3 ON T2.N_Trans=T3.N_Trans and T1.subj_ID=T3.subj_ID Where T2.T_ID = 1 GROUP BY T3.T_ID,T1.subj_ID, T3.N_Trans "
    Set Rs = Cnn.OpenRecordset(StrSql)
    I have been struggling for long hours to produce this output but no succes.

    Subj TotalPrice Payments Remaining
    Phys 35 10 25
    Phys 35 10 15
    Maths 20 10 10

    (30 +40) * 50/100 = 35
    40 *50/100 = 20
    2 payments were made for phys (10 + 10)
    1 payment was made for Maths (10)
    Last edited by newbie2; Feb 6th, 2024 at 04:05 PM.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    This is one convoluted setup i‘ve rarely seen.

    my way:

    teacher is in a m:m relation with subjects —> this is a „class“. A teacher can have multiple subjects, a subject can have multiple teachers. The fee goes into the m:m „class“ table
    “what’s the total price for this subject taught by that teacher“
    optional: the fee could go into the subject table, if it describes the Price for the subject itself, independent who is teaching it

    student is in a m:m relation with „class“. A student can be in multiple classes, a class can have multiple students

    Student is in a 1:m relation with transactions. A student pays multiple fees
    „student_class“ is in a 1:m relation with transactions. A student _class can receive multiple transactions
    transactions needs 2 foreign keys, identifying which student has paid for which class

    will have to think on it
    Last edited by Zvoni; Feb 6th, 2024 at 04:49 PM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,586

    Re: Another complicated query but perhaps possible

    Might help if you gave your tables some meaningful names ... "tbl1" doesn't tell anyone what's in it ... call it what it is... and I don't know what that is so... subj_table is marginally better... "subject" would be best...
    But...

    If yolu have teachers that teach a suvject, then the subject id should be part of the teacher table... how ever, since you can have a teacher with multoiple subjects, you should have a "subject" table, a "teacher" table, and a "teacher_subject" table. The subject table is a table of subjects. and the teacher is the list of teachers ... teacher_subject then links the two in a many-to-many relationship. A teacher can have multiple subjects, and a subject can have multiple teachers.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Tg,
    do you see the same thing as me in his table1?
    student id=2 has a different total price to pay than student id=1 for the same subject with the same teacher?

    crap. That would mean „individual“ pricing
    would mean in my setup above, the fee would go into the student-class connector table, right?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,586

    Re: Another complicated query but perhaps possible

    I see it now... I kinda glossed over because I couldn't tell what tables were what ... but yeah... I see that... and that....bleh... is not good. This is why tables and fields should have reasonable names. "Subject" "Student" "Teacher" "Student_class" "Payments" ... and so on...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Zvoni and techgnome
    thank you for your precious remarks
    Indeed my tables in my database have meaningful names.
    But because these are temporary tables I have given temporary names , I have even dropped some fields and joins.
    I just kept what I thought necessary.
    I consider this as negligence from my part.
    student id=2 has a different total price to pay than student id=1 for the same subject with the same teacher?
    I'm sorry I was not so careful.
    Students pay the same price for the same subject.

    Well in my last thread Zvoni and jdelano suggested good codes. Both are working but the codes deal with one case when a teacher has one subject.
    Now I'm faced with a situation where a teacher has two subjects.
    I tried to modify the codes but I failed.
    Quote Originally Posted by jdelano View Post
    Dim SQL As String
    SQL = "SELECT P1.p1sum,L1.Sfees, P1.T_ID, P1.p1sum -L1.Sfees as res_sum " & _
    " FROM (SELECT T_ID, SUM(TFees) as p1sum FROM Tbl1 P1 GROUP BY P1.T_ID) P1 " & _
    " INNER JOIN (SELECT T_ID, Sfees FROM Tbl2 L1 ) L1 " & _
    " ON P1.T_ID= L1.T_ID " & _
    " ORDER BY P1.T_ID DESC"

    recCount = 1
    runningRemaining = 0

    MSFG.TextMatrix(0, 0) = "TFeeSum"
    MSFG.TextMatrix(0, 1) = "SFee"
    MSFG.TextMatrix(0, 2) = "Remaining"

    Do While Not rs.EOF
    If recCount = 1 Then
    TFeesSum = rs(0) ' grab the TFeesSum from the first record
    runningRemaining = rs(0)
    End If

    MSFG.TextMatrix(recCount, 0) = TFeesSum
    MSFG.TextMatrix(recCount, 1) = rs(1)

    runningRemaining = runningRemaining - rs(1)
    MSFG.TextMatrix(recCount, 2) = runningRemaining
    rs.MoveNext

    recCount = recCount + 1
    Loop


    [/CODE]
    [QUOTE=Zvoni;5631686]Let's get this one sorted out first

    Code:
    SELECT T1.p1sum, T2.SFees, T1.p1sum-SUM(T2.SFees) OVER(PARTITION BY T2.T_ID ORDER BY T2.ROWID) As res_sum
    FROM (SELECT T_ID, SUM(TFees) AS p1sum FROM Tbl1 GROUP BY T_ID) AS T1     
    INNER JOIN Tbl2 AS T2 ON T1.T_ID=T2.T_ID
    returns:

    p1sum SFees res_sum
    30.0 4.0 26.0
    30.0 2.0 24.0
    30.0 3.0 21.0

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Right.
    Here is a Proof of Concept for my idea.

    Newbie2, as tg stated: Give meaningful names to your samples, and give plenty of sample records.

    Here is the fiddle for everyone, who doesn't want to execute the DDL's or doesn't have SQLite:
    https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/0

    Here's the Code to create and populate everything
    Code:
    CREATE TABLE "tbl_student" (
        "ID"    INTEGER,
        "STName"    TEXT,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_subject" (
        "ID"    INTEGER,
        "SName"    TEXT,
        "SubjectPrice"    REAL DEFAULT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher" (
        "ID"    INTEGER,
        "TName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher_subject" (
        "ID"    INTEGER,
        "Teacher_ID"    INTEGER,
        "Subject_ID"    INTEGER,
        "ClassPrice"    REAL DEFAULT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE "tbl_transaction" (
        "ID"    INTEGER,
        "Payment"    REAL,
        "PayDate"    TEXT,
        "Student_Class_ID"    INTEGER,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('1', 'Adam Sandler');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('2', 'Elijah Wood');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('3', 'Nicole Kidman');
    
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('1', 'James Cameron');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('2', 'Steven Spielberg');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('3', 'Stanley Kubrick');
    
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('1', 'Physics', '100.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('2', 'Math', NULL);
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('3', 'English', '300.0');
    
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('1', '1', '1', '80.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('2', '1', '3', '70.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('3', '2', '2', '60.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('4', '3', '3', '65.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('5', '2', '1', '100.0');
    
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('1', '1', '1', '30.0');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('2', '1', '3', '40.0');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('3', '2', '5', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('4', '3', '2', '20.0');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('5', '1', '4', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('6', '2', '2', NULL);
    
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');
    Here's the SQL for the Query:
    Code:
    SELECT 
    ST.STName As Student, S.SName As Subject, T.TName As Teacher, 
    COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice) As TotalPrice, 
    COALESCE(TR.Payment, 0) As Payment, TR.PayDate,
    COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice)-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As Remaining
    FROM tbl_student AS ST 
    INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID 
    INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
    INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
    LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
    ORDER BY St.ID, SC.ID, TR.PayDate
    Returns:
    Student Subject Teacher TotalPrice Payment PayDate Remaining
    Adam Sandler Physics James Cameron 30.0 10.0 2023-01-10 20.0
    Adam Sandler Physics James Cameron 30.0 10.0 2023-02-12 10.0
    Adam Sandler Physics James Cameron 30.0 10.0 2023-02-28 0.0
    Adam Sandler Math Steven Spielberg 40.0 20.0 2023-02-06 20.0
    Adam Sandler English Stanley Kubrick 65.0 60.0 2023-01-24 5.0
    Elijah Wood Physics Steven Spielberg 100.0 15.0 2023-02-25 85.0
    Elijah Wood English James Cameron 70.0 0 70.0
    Nicole Kidman English James Cameron 20.0 30.0 2023-02-09 -10.0


    How it works:
    A Subject has a "Price" (Say, determined by the School)
    A Teacher has a Price for a Subject, which can differ from the "general" Price for a Subject
    A Student can negotiate an individual Price for a Subject with a specific teacher.

    The COALESCE on the Prices works as follows: If a Student has an individual Price, return that one, if No, does the teacher has his "own" Price for a Subject, If no, return the "general" Price for the Subject

    EDIT: Just noticed: The COALESCE for the Prices needs a fourth Parameter "0" in case all 3 Prices are NULL.
    In that case that "Class" would be "free of Charge"
    COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice, 0) As TotalPrice....
    ....
    COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice, 0)-SUM(COALESCE(....
    Last edited by Zvoni; Feb 7th, 2024 at 04:33 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Zvoni
    You are so generous.
    Thank you very much for sharng this valuable work.
    I'm now travelling and I 'll test this once at home.
    Just small remarks:
    A teacher payment is not static. It s calculted per number of sessions he will teach.
    A subject has not th same price for teachers. A well-experienced teacher is not paid the same price as a less experienced one for the same subject.
    The teacher's payments may be done in installments.
    I don't know whether the code takes this into consederation.
    thank you.

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Zvoni
    A teacher payment is not static. It s calculted per number of sessions he will teach.
    A subject has not th same price for teachers. A well-experienced teacher is not paid the same price as a less experienced one for the same subject.
    The teacher's payments may be done in installments.
    I don't know whether the code takes this into consederation.
    thank you.

    See my post
    A Subject has a "Price" (Say, determined by the School)
    A Teacher has a Price for a Subject, which can differ from the "general" Price for a Subject
    A Student can negotiate an individual Price for a Subject with a specific teacher.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Zvoni
    I 'm still away from home and I haven't tested the demo yet. Perhaps I'll do tomorrow .
    I have a slight worry :
    Do I need to have another transaction table for the teachers payments?
    Of course a teacher accumulates his pay from the students fees for the subject he teaches.
    Also The teacher's payments may be done in installments.
    thank you very much

  11. #11
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Zvoni
    I 'm still away from home and I haven't tested the demo yet. Perhaps I'll do tomorrow .
    I have a slight worry :
    Do I need to have another transaction table for the teachers payments?
    Of course a teacher accumulates his pay from the students fees for the subject he teaches.
    Also The teacher's payments may be done in installments.
    thank you very much
    No, it's exactly as i've written above: You only have one transaction-table and that one connects to the "Student-Class" Table.
    In other words: The student is not paying the Teacher per se, he is paying the "class", which is a combination of a specific subject and a specific teacher.

    If you have the requirement, e.g. James Cameron is teaching "Math" and he demands 100USD, independant, how many students he has in that class, then we have to expand the query with a Count of Students, but that's not black magic, either.
    Say, James Cameron in Math wants 100USD, and he has 5 Students, so each student would need to pay 20USD in Installments of 5USD is pretty easy to expand
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Mr Zvoni
    Please excuse my ignorance
    Sorry i'm still away from home and I'm writing from my mobile
    Imagine a teacher has accumulated $100
    The school pays him only $50 for the current week. The remaining is $50
    Where am I going to store these installments?
    Thank you very much

  13. #13
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Mr Zvoni
    Please excuse my ignorance
    Sorry i'm still away from home and I'm writing from my mobile
    Imagine a teacher has accumulated $100
    The school pays him only $50 for the current week. The remaining is $50
    Where am I going to store these installments?
    Thank you very much
    I don't understand this.
    Who is paying the Teacher? The School or the Students?
    Because in your inquiries and code-samples the students pay the teachers fee, not the school
    So where do the 100USD/50USD now come from?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    I don't understand this.
    Who is paying the Teacher? The School or the Students?
    Because in your inquiries and code-samples the students pay the teachers fee, not the school
    So where do the 100USD/50USD now come from?
    The students pay the school and the school pays the teachers.
    A teacher may take 50% of his students's fees another one may take 60% and so on.
    The school may give the teacher the whole wage as they can pay him in installments. I mean several payments.
    Last edited by newbie2; Feb 8th, 2024 at 07:07 AM.

  15. #15
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    The students pay the school and the school pays the teachers.
    A teacher may take 50% of his students's fees another one may take 60% and so on.
    The school may give the teacher the whole wage as they can pay him in installments. I mean several payments.
    Ahhh.... OK...let me think on it, but i don't really see any serious problems

    Just to see, if i understood everything correctly:

    The School says "English-Class costs 100 $" for all Students
    The School has 2 teachers for English, The first teacher demands 50% of the Fee (leaving 50% for the School), the other teacher 75% (leaving 25% for the School)

    The School says "Math costs 200 $" for all Students
    The School has a teacher, who teaches both, Math and English
    The teacher demands for English 50%, for Math 60%

    That correct?

    EDIT: I see two ways to skin that cat
    1) as a percentage ("50% of 100 $") --> That one would involve multiplications on the fly within the SQL-Query
    2) as an absolute ("50 $ of those 100 $") --> That one would involve a simple subtraction

    If you want to see the "payable" Transactions from School to teacher, then yes, you need a second Transaction-Table (which IMO would be the proper way)
    Last edited by Zvoni; Feb 8th, 2024 at 07:43 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  16. #16
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,290

    Re: Another complicated query but perhaps possible

    I really hope this is not a prodcution system but just some sample code as an excercise. . . This is totally messed up already on conceptual level :-))

    cheers,
    </wqw>

  17. #17

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Many many thanks for understanding me
    that's what I'm wishing to do

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Would you please sir help me with building the teacher_transaction table and the foreign keys?

  19. #19
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Slightly changed fiddle:
    https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/3

    DDL
    Code:
    CREATE TABLE "tbl_student" (
        "ID"    INTEGER,
        "STName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_subject" (
        "ID"    INTEGER,
        "SName"    TEXT NOT NULL,
        "SubjectPrice"    REAL NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher" (
        "ID"    INTEGER,
        "TName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher_subject" (
        "ID"    INTEGER,
        "Teacher_ID"    INTEGER,
        "Subject_ID"    INTEGER,
        "ClassPrice"    REAL NOT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE "tbl_transaction" (
        "ID"    INTEGER,
        "Payment"    REAL,
        "PayDate"    TEXT,
        "Student_Class_ID"    INTEGER,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('1', 'Adam Sandler');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('2', 'Elijah Wood');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('3', 'Nicole Kidman');
    
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('1', 'James Cameron');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('2', 'Steven Spielberg');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('3', 'Stanley Kubrick');
    
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('1', 'Physics', '100.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('2', 'Math', '200.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('3', 'English', '300.0');
    
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('1', '1', '1', '80.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('2', '1', '3', '70.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('3', '2', '2', '60.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('4', '3', '3', '65.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "ClassPrice") VALUES ('5', '2', '1', '100.0');
    
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('1', '1', '1', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('2', '1', '3', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('3', '2', '5', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('4', '3', '2', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('5', '1', '4', NULL);
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price") VALUES ('6', '2', '2', NULL);
    
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');
    SQL-Query
    Code:
    SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, 
    COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
    S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
    TR.PayDate,
    T.TName As Teacher, 
    TS.ClassPrice As TeacherPrice,
    COALESCE(TS.ClassPrice/S.SubjectPrice*TR.Payment, 0) As PaymentBySchoolToTeacher,
    --100*TS.ClassPrice/S.SubjectPrice As RatioPercent,
    TS.ClassPrice-SUM(COALESCE(TS.ClassPrice/S.SubjectPrice*TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, 
    TR.PayDate
    FROM tbl_student AS ST 
    INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID 
    INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
    INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
    LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
    ORDER BY St.ID, SC.ID, TR.PayDate
    Returns
    Student Subject SubjectPrice PaymentByStudentToSchool RemainingSubjectPrice PayDate Teacher TeacherPrice PaymentBySchoolToTeacher RemainingTeacherPrice PayDate
    Adam Sandler Physics 100.0 10.0 90.0 2023-01-10 James Cameron 80.0 8.0 72.0 2023-01-10
    Adam Sandler Physics 100.0 10.0 80.0 2023-02-12 James Cameron 80.0 8.0 64.0 2023-02-12
    Adam Sandler Physics 100.0 10.0 70.0 2023-02-28 James Cameron 80.0 8.0 56.0 2023-02-28
    Adam Sandler Math 200.0 20.0 180.0 2023-02-06 Steven Spielberg 60.0 6.0 54.0 2023-02-06
    Adam Sandler English 300.0 60.0 240.0 2023-01-24 Stanley Kubrick 65.0 13.0 52.0 2023-01-24
    Elijah Wood Physics 100.0 15.0 85.0 2023-02-25 Steven Spielberg 100.0 15.0 85.0 2023-02-25
    Elijah Wood English 300.0 0 300.0 James Cameron 70.0 0 70.0
    Nicole Kidman English 300.0 30.0 270.0 2023-02-09 James Cameron 70.0 7.0 63.0 2023-02-09


    Don't forget the Sanity-Checks (Teacher's fee cannot be higher than the Fee the School demands, etc.)

    Note: I've left the option for "individual" Prices for individual Students. Here the Sanity-Check should be:
    Individual Price is BETWEEN SchoolPrice ("SubjectPrice") and TeachersPrice -->
    Individual Price can be lower than School's Price for Subject, but must be higher than teacher's price

    Note2: This is WITHOUT a second Transaction-Table "SchoolToTeacher" --> It implies that any Payment from Student to School gets forwarded from School to Teacher (adjusted by "ratio") IMMEDIATELY!
    If the Payment SchoolToTeacher is NOT synchronized with payment StudentToSchool, THEN you need a second transaction-table
    Last edited by Zvoni; Feb 8th, 2024 at 10:49 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  20. #20

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    you are so generous sir
    All words of the word fail to thank you enough
    I've just come home. I'm so tired.
    Tomorrow morning I'll proceed with the new conception
    thank you million times.

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Hello Zvoni
    Sorry to disturb you again
    I'm having some problems.
    1st problem in calculating students' fees.
    students' fees are calculated by muliplying the price of the subject with the number of sessions the student has atteded.
    For this, I added a column in tbl_student_class called NumOfSession.

    Code:
    StrSql = "SELECT TR.PayDate, TR.ID, SC.Price, TR.Payment  ," & _
    " ST.STName As Student, S.SName As Subject, T.TName As Teacher," & _
    " COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice) * Num_sessions As TotalPrice," & _
    " COALESCE(TR.Payment, 0)  As Payment, " & _
    " COALESCE(SC.Price, TS.ClassPrice,S.SubjectPrice)- SUM(COALESCE(TR.Payment, 0)* Num_sessions )" & _
    " OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As Remaining " & _
    " FROM tbl_student AS ST " & _
    "INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID " & _
    " INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID " & _
    " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
    " INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID " & _
    " LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID " & _
    " Where St.ID = " & NT(Text5.Text, 0) & " and " & _
    " TS.Subject_ID = " & NT(Text6.Text, 0) & " and " & _
    " SC.Class_ID = " & NT(Text8.Text, 0) & " " & _
    " ORDER BY St.ID, SC.ID, TR.PayDate"
    Set Rs = Cnn.OpenRecordset(StrSql)
    the result is this:
    Subject Price payments remaing
    Physics 200 10 90
    Physics 200 15 75

    Where it should be:
    Subject Price payments remaing
    Physics 200 10 190
    Physics 200 15 175

  22. #22
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    You have to insert your numsession factor in line 5 of your query to the first operand.
    look closely at what’s left of sum over (left of the minus): it‘s the same operand as in line 3.
    you‘re applying your factor to the payment. Wrong.
    the factor only applies to totalprice, as in line 3

    and qualify your num_session column with the correct alias SC
    Last edited by Zvoni; Feb 10th, 2024 at 11:37 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    You have to insert your numsession factor in line 5 of your query to the first operand.
    look closely at what’s left of sum over (left of the minus): it‘s the same operand as in line 3.
    you‘re applying your factor to the payment. Wrong.
    the factor only applies to totalprice, as in line 3

    and qualify your num_session column with the correct alias SC
    Thank you very much

  24. #24

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    The second issue is related to calculating the teachers ' wages.

    The teachers ' wages are calculated by the sums of students fees stored in tbl_transaction
    but I need to subtract the school part and also subtract any other previous payment stored in Teacher_transaction_tbl.
    For this I added a new Teacher_transaction_tbl and a new column in tbl_teacher_subject called (teacher_percentage).
    To calculate a teachers wage.

    teachers wage = sum (sts_fees) * teacher_percentage /100 - sum (payments already made for a specific subject )
    Example:
    James Cameron has accumulated 100 USD for physics..
    His percentage for this subject in teacher_percentage = 60 (60%)
    He has already received 40 USD
    To calculate the new wage is: 100 *60/100 - 40 = 20

    An other scenario is that
    James Cameron has accumulated 200 USD for English.
    He hasn't received any payments yet.
    His percentage in teacher_percentage = 50 (50%)
    He has no reference in Teacher_transaction_tbl with this subject.
    To calculate his wage is: 200 *50/100 = 100
    Here I'm stuck.
    thank you a lot
    Last edited by newbie2; Feb 10th, 2024 at 02:59 PM.

  25. #25
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    200*50/100 is 100 not 50

    will be Monday when i can have a look
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  26. #26

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Zvoni View Post
    200*50/100 is 100 not 50

    will be Monday when i can have a look
    You are right
    take your time sir

  27. #27
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    To get that first thing (TeacherPercentage and NumOfSessions) out of the Way.

    Changed fiddle.
    https://www.db-fiddle.com/f/8H8t8hziQ6p6rDi4UBj1vu/4

    Some changed and added columns
    DDL
    Code:
    CREATE TABLE "tbl_student" (
        "ID"    INTEGER,
        "STName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_subject" (
        "ID"    INTEGER,
        "SName"    TEXT NOT NULL,
        "SubjectPrice"    REAL NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher" (
        "ID"    INTEGER,
        "TName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher_subject" (
        "ID"    INTEGER,
        "Teacher_ID"    INTEGER,
        "Subject_ID"    INTEGER,
        "TeacherPercentage"    REAL NOT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        "NumOfSessions" INTEGER DEFAULT 1,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE "tbl_transaction" (
        "ID"    INTEGER,
        "Payment"    REAL,
        "PayDate"    TEXT,
        "Student_Class_ID"    INTEGER,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('1', 'Adam Sandler');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('2', 'Elijah Wood');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('3', 'Nicole Kidman');
    
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('1', 'James Cameron');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('2', 'Steven Spielberg');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('3', 'Stanley Kubrick');
    
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('1', 'Physics', '100.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('2', 'Math', '200.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('3', 'English', '300.0');
    
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('1', '1', '1', '50.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('2', '1', '3', '25.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('3', '2', '2', '75.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('4', '3', '3', '50.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('5', '2', '1', '25.0');
    
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('1', '1', '1', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('2', '1', '3', NULL,'2');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('3', '2', '5', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('4', '3', '2', NULL,'3');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('5', '1', '4', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('6', '2', '2', NULL,'2');
    
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('1', '10.0', '2023-01-10', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('2', '20.0', '2023-02-06', '2');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('3', '30.0', '2023-02-09', '4');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('4', '10.0', '2023-02-12', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('5', '15.0', '2023-02-25', '3');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('6', '10.0', '2023-02-28', '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID") VALUES ('7', '60.0', '2023-01-24', '5');
    SQL
    Code:
    SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, 
    SC.NumOfSessions,
    S.SubjectPrice*SC.NumOfSessions As SubjectTotal,
    COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
    SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
    TR.PayDate,
    T.TName As Teacher, 
    TS.TeacherPercentage As TeacherPercentage,
    TS.TeacherPercentage/100*S.SubjectPrice*SC.NumOfSessions AS TeacherTotal,
    COALESCE(TS.TeacherPercentage/100*TR.Payment, 0) As PaymentBySchoolToTeacher,
    TS.TeacherPercentage/100*SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TS.TeacherPercentage/100*TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, 
    TR.PayDate
    FROM tbl_student AS ST 
    INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID 
    INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
    INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
    LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
    ORDER BY St.ID, SC.ID, TR.PayDate
    Returns:
    Student Subject SubjectPrice NumOfSessions SubjectTotal PaymentByStudentToSchool RemainingSubjectPrice PayDate Teacher TeacherPercentage TeacherTotal PaymentBySchoolToTeacher RemainingTeacherPrice PayDate
    Adam Sandler Physics 100.0 1 100.0 10.0 90.0 2023-01-10 James Cameron 50.0 50.0 5.0 45.0 2023-01-10
    Adam Sandler Physics 100.0 1 100.0 10.0 80.0 2023-02-12 James Cameron 50.0 50.0 5.0 40.0 2023-02-12
    Adam Sandler Physics 100.0 1 100.0 10.0 70.0 2023-02-28 James Cameron 50.0 50.0 5.0 35.0 2023-02-28
    Adam Sandler Math 200.0 2 400.0 20.0 380.0 2023-02-06 Steven Spielberg 75.0 300.0 15.0 285.0 2023-02-06
    Adam Sandler English 300.0 1 300.0 60.0 240.0 2023-01-24 Stanley Kubrick 50.0 150.0 30.0 120.0 2023-01-24
    Elijah Wood Physics 100.0 1 100.0 15.0 85.0 2023-02-25 Steven Spielberg 25.0 25.0 3.75 21.25 2023-02-25
    Elijah Wood English 300.0 2 600.0 0 600.0 James Cameron 25.0 150.0 0 150.0
    Nicole Kidman English 300.0 3 900.0 30.0 870.0 2023-02-09 James Cameron 25.0 225.0 7.5 217.5 2023-02-09



    Now the pertinent question: Is the teacher payed "asynchronously" to the Students payments?
    If yes, then you need "separate" transactions to the students transactions.
    I have an idea to solve that with the already existing transaction-table.
    Will have to test for feasabilty
    Last edited by Zvoni; Feb 12th, 2024 at 02:24 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  28. #28
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    OK, did some tests.

    If your teacher's payment is "asynchronous" it's not going to be possible to show that in one single query, because, in a nutshell, it's uncorrelated Data

    e.g. English with James Cameron
    he has 2 Students. E. Wood with 600$ total (2 sessions at 300$), and N.Kidman with 900$ total (3 sessions at 300$), making it 1500$ Total overall, with 25% of it going to the Teacher, making it 375$ (25% of 1500$)

    Elijah Wood has not paid anything for his 2 sessions right now
    Nicole Kidman has paid one installment for her three sessions on 2023-02-09, leaving her at 870$ remaining total

    Now imagine Elijah Wood paying his first installment of 100$ on 2023-02-15. He'd have a remaining total of 500$

    If the School now pays James Cameron "accumulated" 32,50$ (25% of 100$ and 25% of 30$) on say 2023-02-20, how would you correlate Data?
    This only works in separate queries, and has nothing to do with if it's two transaction tables or one (and Yes, my idea does work with one transaction-table)

    btw: Is "NumOfSessions" individual per Student ("E.Wood needs 2 Sessions, N.Kidman needs 3 Sessions"), or is it per "Class" ("English is taught in 3 Sessions for all Students")?
    Last edited by Zvoni; Feb 12th, 2024 at 09:51 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  29. #29

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Billions thanks sir
    You are really great man.
    I have tested the code as a whole and it worked like a charm.
    I need some time to split the code and apply it to specific situations.
    Code:
    Now the pertinent question: Is the teacher payed "asynchronously" to the Students payments?
    I do not know whether i have understood your question well or not but I think the answer is yes as described in post 23.

    Code:
    I have an idea to solve that with the already existing transaction-table.
    Will have to test for feasabilty
    That will be something of magic.

    Million thanks again

  30. #30

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    I'm sorry I have just seen your last post.
    btw: Is "NumOfSessions" individual per Student ("E.Wood needs 2 Sessions, N.Kidman needs 3 Sessions"),
    or is it per "Class" ("English is taught in 3 Sessions for all Students")?
    It is individual.
    one student may study 6 sessions for English but another one may enroll for 10 for the same subject.
    Last edited by newbie2; Feb 12th, 2024 at 01:21 PM.

  31. #31

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    I think I start to get my happiness.
    I created a new table "Transaction_teacher_tbl"
    Code:
    CREATE TABLE "Transaction_teacher_tbl" (
        "ID"    INTEGER,
        "Teacher_ID"    INTEGER,
        "Payment"    REAL,
        "PayDate"    DateTime,
        "Subj_ID"    INTEGER ,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Subj_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    Code:
    INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate","Subj_ID" ) VALUES ('1', '1', '20', '2023-01-10',1);
    INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate", "Subj_ID") VALUES ('2', '1', '15', '2023-01-11',1);
    INSERT INTO "Transaction_teacher_tbl" ("ID", "Teacher_ID", "Payment", "PayDate", "Subj_ID") VALUES ('3', '1', '', '2023-01-12',3);
    Code:
    StrSql = "SELECT  S.SName As Subject,  " & _
    " T.TName As Teacher, " & _
    " TS.TeacherPercentage As TeacherPercentage, " & _
    " TS.TeacherPercentage/100*S.SubjectPrice*SC.NumOfSessions AS TeacherTotal, " & _
    " COALESCE(TR.Payment, 0) As PaymentBySchoolToTeacher," & _
    " TS.TeacherPercentage/100*SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingTeacherPrice, " & _
    " TR.PayDate " & _
    " FROM tbl_teacher AS T " & _
    " INNER JOIN tbl_teacher_subject AS TS ON TS.Teacher_ID=T.ID " & _
    " INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID " & _
    " INNER JOIN tbl_student_class AS SC ON SC.Class_ID=S.ID " & _
    "  LEFT JOIN Transaction_teacher_tbl AS TR ON TR.Subj_ID=TS.Subject_ID and TR.Teacher_ID=TS.Teacher_ID " & _
    " ORDER BY T.ID, TS.ID, TR.PayDate"
    Set Rs = Cnn.OpenRecordset(StrSql)
    I'm getting this output which is what I have been struggling for days to get it.
    However I still need more time to deal with similar transactions.
    Attachment 190456
    Name:  -00.png
Views: 94
Size:  8.4 KB
    Last edited by newbie2; Feb 12th, 2024 at 03:59 PM.

  32. #32
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Right, there is something weird going on with the Forum.
    Can't post the DDL for a specific Constraint?!?!?!

    Anyway,
    my Solution with only one Transaction-table
    Let's see if anyone can spot the "magic"
    DDL
    Code:
    CREATE TABLE "tbl_student" (
        "ID"    INTEGER,
        "STName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_subject" (
        "ID"    INTEGER,
        "SName"    TEXT NOT NULL,
        "SubjectPrice"    REAL NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher" (
        "ID"    INTEGER,
        "TName"    TEXT NOT NULL,
        PRIMARY KEY("ID")
    );
    
    CREATE TABLE "tbl_teacher_subject" (
        "ID"    INTEGER,
        "Teacher_ID"    INTEGER,
        "Subject_ID"    INTEGER,
        "TeacherPercentage"    REAL NOT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Subject_ID") REFERENCES "tbl_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Teacher_ID") REFERENCES "tbl_teacher"("ID") ON UPDATE CASCADE ON DELETE RESTRICT
    );
    
    CREATE TABLE "tbl_student_class" (
        "ID"    INTEGER,
        "Student_ID"    INTEGER,
        "Class_ID"    INTEGER,
        "Price"    REAL DEFAULT NULL,
        "NumOfSessions" INTEGER DEFAULT 1,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Class_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
        FOREIGN KEY("Student_ID") REFERENCES "tbl_student"("ID") ON UPDATE CASCADE ON DELETE CASCADE
    );
    
    CREATE TABLE "tbl_transaction" (
        "ID"    INTEGER,
        "Payment"    REAL,
        "PayDate"    TEXT,
        "Student_Class_ID"    INTEGER DEFAULT NULL,
        "Teacher_Subject_ID"    INTEGER DEFAULT NULL,
        PRIMARY KEY("ID"),
        FOREIGN KEY("Student_Class_ID") REFERENCES "tbl_student_class"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
        FOREIGN KEY("Teacher_Subject_ID") REFERENCES "tbl_teacher_subject"("ID") ON UPDATE CASCADE ON DELETE RESTRICT,
        CONSTRAINT "CheckForeignKeyXOR" CHECK((Student_Class_ID IS NULL AND teacher_Subject_ID IS NOT NULL) OR (Student_Class_ID IS NOT NULL AND Teacher_Subject_ID IS NULL))
    )
    
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('1', 'Adam Sandler');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('2', 'Elijah Wood');
    INSERT INTO "tbl_student" ("ID", "STName") VALUES ('3', 'Nicole Kidman');
    
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('1', 'James Cameron');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('2', 'Steven Spielberg');
    INSERT INTO "tbl_teacher" ("ID", "TName") VALUES ('3', 'Stanley Kubrick');
    
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('1', 'Physics', '100.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('2', 'Math', '200.0');
    INSERT INTO "tbl_subject" ("ID", "SName", "SubjectPrice") VALUES ('3', 'English', '300.0');
    
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('1', '1', '1', '50.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('2', '1', '3', '25.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('3', '2', '2', '75.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('4', '3', '3', '50.0');
    INSERT INTO "tbl_teacher_subject" ("ID", "Teacher_ID", "Subject_ID", "TeacherPercentage") VALUES ('5', '2', '1', '25.0');
    
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('1', '1', '1', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('2', '1', '3', NULL,'2');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('3', '2', '5', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('4', '3', '2', NULL,'3');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('5', '1', '4', NULL,'1');
    INSERT INTO "tbl_student_class" ("ID", "Student_ID", "Class_ID", "Price", "NumOfSessions") VALUES ('6', '2', '2', NULL,'2');
    
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('1', '10.0', '2023-01-10', '1', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('2', '20.0', '2023-02-06', '2', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('3', '30.0', '2023-02-09', '4', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('4', '10.0', '2023-02-12', '1', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('5', '15.0', '2023-02-25', '3', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('6', '10.0', '2023-02-28', '1', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('7', '60.0', '2023-01-24', '5', NULL);
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('8', '30.0', '2023-03-05', NULL, '1');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('9', '30.0', '2023-02-15', NULL, '2');
    INSERT INTO "tbl_transaction" ("ID", "Payment", "PayDate", "Student_Class_ID", "Teacher_Subject_ID") VALUES ('10', '25.0', '2023-03-15', NULL, '1');
    Query to return Students payments
    Code:
    SELECT ST.STName As Student, S.SName As Subject, S.SubjectPrice As SubjectPrice, T.TName As TeacherName,
    SC.NumOfSessions,
    S.SubjectPrice*SC.NumOfSessions As SubjectTotal,
    COALESCE(TR.Payment, 0) As PaymentByStudentToSchool, 
    SC.NumOfSessions*S.SubjectPrice-SUM(COALESCE(TR.Payment, 0)) OVER(PARTITION BY SC.ID ORDER BY TR.PayDate) As RemainingSubjectPrice,
    TR.PayDate
    FROM tbl_student AS ST 
    INNER JOIN tbl_student_class AS SC ON SC.Student_ID=ST.ID 
    INNER JOIN tbl_teacher_subject AS TS ON TS.ID=SC.Class_ID 
    INNER JOIN tbl_subject AS S ON S.ID=TS.Subject_ID
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID 
    LEFT JOIN tbl_transaction AS TR ON TR.Student_Class_ID=SC.ID 
    ORDER BY St.ID, SC.ID, TR.PayDate
    Returns
    Student Subject SubjectPrice TeacherName NumOfSessions SubjectTotal PaymentByStudentToSchool RemainingSubjectPrice PayDate
    Adam Sandler Physics 100.0 James Cameron 1 100.0 10.0 90.0 2023-01-10
    Adam Sandler Physics 100.0 James Cameron 1 100.0 10.0 80.0 2023-02-12
    Adam Sandler Physics 100.0 James Cameron 1 100.0 10.0 70.0 2023-02-28
    Adam Sandler Math 200.0 Steven Spielberg 2 400.0 20.0 380.0 2023-02-06
    Adam Sandler English 300.0 Stanley Kubrick 1 300.0 60.0 240.0 2023-01-24
    Elijah Wood Physics 100.0 Steven Spielberg 1 100.0 15.0 85.0 2023-02-25
    Elijah Wood English 300.0 James Cameron 2 600.0 0 600.0
    Nicole Kidman English 300.0 James Cameron 3 900.0 30.0 870.0 2023-02-09
    Viggo Mortensen Physics 100.0 James Cameron 2 200.0 0 200.0

    Query to return teacher's Payment
    Code:
    WITH 
        SC    AS (SELECT Class_ID, SUM(NumOfSessions) AS NumOfSessions FROM tbl_student_class
                GROUP BY Class_ID)
    
    SELECT 
    S.SName As SubjectName, S.SubjectPrice, T.TName As TeacherName, TS.TeacherPercentage, 
    S.SubjectPrice*TS.TeacherPercentage/100 As TeacherPrice,
    COALESCE(SC.NumOfSessions, 0) As TotalSessions,
    S.SubjectPrice*TS.TeacherPercentage/100*COALESCE(SC.NumOfSessions, 0) As TotalTeacherPrice,
    COALESCE(TR.Payment,0) AS Payment,
    S.SubjectPrice*TS.TeacherPercentage/100*COALESCE(SC.NumOfSessions, 0)-SUM(COALESCE(TR.Payment,0)) OVER(PARTITION BY SC.Class_ID ORDER BY TR.PayDate) As TotalTeacherPriceRemaining,
    TR.Paydate 
     
    
    FROM tbl_subject As S 
    INNER JOIN tbl_teacher_subject As TS ON TS.Subject_ID=S.ID 
    INNER JOIN tbl_teacher AS T ON T.ID=TS.Teacher_ID
    LEFT JOIN SC ON SC.Class_ID=TS.ID
    LEFT JOIN tbl_transaction As TR ON TR.Teacher_Subject_ID=TS.ID
    Returns
    SubjectName SubjectPrice TeacherName Teacher Percentage TeacherPrice TotalSessions Total TeacherPrice Payment TotalTeacher PriceRemaining PayDate
    Physics 100.0 James Cameron 50.0 50.0 3 150.0 30.0 120.0 2023-03-05
    Physics 100.0 James Cameron 50.0 50.0 3 150.0 25.0 95.0 2023-03-15
    English 300.0 James Cameron 25.0 75.0 5 375.0 30.0 345.0 2023-02-15
    Math 200.0 Steven Spielberg 75.0 150.0 2 300.0 0 300.0
    English 300.0 Stanley Kubrick 50.0 150.0 1 150.0 0 150.0
    Physics 100.0 Steven Spielberg 25.0 25.0 1 25.0 0 25.0
    Last edited by Zvoni; Feb 13th, 2024 at 03:24 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  33. #33
    Administrator Steve R Jones's Avatar
    Join Date
    Apr 2012
    Location
    Largo, FL.
    Posts
    1,904

    Re: Another complicated query but perhaps possible

    test - Posting works for me.

    Post #27 - I recommend you remove that if possible...
    Wi-fi went down for five minutes, so I had to talk to my family....They seem like nice people.

  34. #34
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by Steve R Jones View Post
    Post #27 - I recommend you remove that if possible...
    Because of the "widespread" of the Forum? Yeah, thought about it.

    But i (seem to?) have figured out what the problem is/was.
    See my post in Forum-Feedback
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  35. #35

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Zvoni
    Thank you very much the magical work you are doing.
    Let's see if anyone can spot the "magic"
    Frankly I am not up to the task to do that.
    Zvoni
    I see that the teacher is payed synchronously to the Students payments which does not fit my situation.
    Thus, I think a Teacher_Transaction table is necessary. as I did in post 31.
    thanks

  36. #36
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Zvoni
    I see that the teacher is payed synchronously to the Students payments which does not fit my situation.
    Thus, I think a Teacher_Transaction table is necessary. as I did in post 31.
    thanks
    No, he's not.
    Look closely at the "Paydate" in my last example (Post 32)
    Compare the Paydate of Student-Payments to the Paydate of Teacher's Payment

    EDIT: The teacher's Payment has nothing to do with Student's Payment.
    It's up to the School HOW much they transfer to the teacher's account
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  37. #37

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    EDIT: The teacher's Payment has nothing to do with Student's Payment.
    It's up to the School HOW much they transfer to the teacher's account
    Looking closely to the code, I figured out that students payments and teachers payments are done seperately in the same table.
    Mr Zvoni:
    A question out of curiosity:
    Which method is simpler but also performant for a newbie:
    Having one transaction table to track both payment?
    or having two seperate transaction tables?
    thank you

  38. #38
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Looking closely to the code, I figured out that students payments and teachers payments are done seperately in the same table.
    Mr Zvoni:
    A question out of curiosity:
    Which method is simpler but also performant for a newbie:
    Having one transaction table to track both payment?
    or having two seperate transaction tables?
    thank you
    For a newbie? two tables. Easier to understand the DDL-Statements

    For someone advanced? One table, with a "specialty" i haven't mentioned above:
    in your scenario i'd store teacher's payments as negative values ("James Cameron was paid -30$")

    Reason:
    Imagine you want to calculate the "net"-result of the School.
    That means to sum up all Student-Payments, but then separately subtract teacher payments
    With two tables you have to use JOINS, with one table, you just add up (see my specialty above)

    The other reason for one table (which is actually IMO the main one):
    You have to think of that transaction-table like a "bank-account"

    Take a look at your own Bank-Account: You have payments coming in (salary etc.), you have payments going out (rent for Appartment etc.), but both happen in one "table" (=Account)

    Remember: A Database should "reflect" the "Environment" (A school) it was designed for "as close as possible to real life"

    Performance-wise? No idea

    EDIT: I have to amend the specialty above.
    I'd introduce an additional Field "TransactionSign" or similar in the transaction-table, which only accepts two values: 1 and -1
    The payment itself would still get stored as a positive value, but depending if it's a Students payment it would get Sign=1, and if it's a Payment to a teacher it would get Sign=-1
    So it would be a simple multiplication, and the "Sign"-Field is never shown anywhere in any statistics.
    I'd even add that Sign-Column as a Generated Column with Attribute STORED, though VIRTUAL should work the same
    Last edited by Zvoni; Feb 13th, 2024 at 10:32 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  39. #39

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2015
    Posts
    935

    Re: Another complicated query but perhaps possible

    Zvoni
    Excuse my weak understanging.
    I think I see another issue .
    I noticed that the teacher's price is retrieved from the tbl_subject (subject_price) however in my situation the teacher's price is accumulated by summing the students' payments.
    For example the price calculted for James Cameron = sum of students payments(for physics) * TeacherPercentage/100*NumOfSessions.
    I don't know whether I understand the code well or not.
    thank you
    Last edited by newbie2; Feb 13th, 2024 at 10:01 AM.

  40. #40
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,543

    Re: Another complicated query but perhaps possible

    Quote Originally Posted by newbie2 View Post
    Zvoni
    Excuse my weak understanging.
    I think I see another issue .
    I noticed that the teacher's price is retrieved from the tbl_subject (subject_price) however in my situation the teacher's price is accumulated by summing the students' payments.
    For example the price calculted for James Cameron = sum of students payments(for physics) * TeacherPercentage/100*SC.NumOfSessions.
    I don't know whether I understand the code well or not.
    thank you
    OK, i'll try to explain:
    Let's take a single Subject (Physics), which the school says: "One session costs 100$"
    Teacher "James Cameron" negotiated a "Teachers price" for "Physics": say "25%" of the Subject Price!!
    Meaning: It doesn't matter how many students with how many sessions individualy book that Class, "James Cameron" will always get 25% of the Subjects Price multiplied with the sessions in total!!

    Scenario A:
    So if there are 2 Students, one student booking 5 sessions, the other 10 sessions, the total "income" of the School is 1500$ (5x100$+10x100$)
    And "James Cameron" will always receive max 25% of those 1500$ as "outgoing" payment = 375$

    Scenario B:
    There is only one student, but this one books 15 sessions. the total "income" of the School is again 1500$ (15x100$)
    And "James Cameron" will again receive max 25% of those 1500$ as "outgoing" payment = 375$

    The teacher doesn't care how many students he has, only how many sessions are booked.

    He could even get paid in one single installment, say at the end of a month or year (or whatever)
    That's what i meant "he gets paid asynchronously to the students payments"
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

Page 1 of 4 1234 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width