Untitled

WITH
            ToneReadingsRelatedTablesJoinedLeft AS
            (SELECT TestId, Gender, Db, TestTime, BirthDate
            FROM ToneReadings
            JOIN ReadingsSets ON ToneReadings.ReadingsSetId = ReadingsSets.Id
            JOIN Tests ON ReadingsSets.TestId = Tests.Id
            JOIN Sessions ON Tests.SessionId = Sessions.Id
            JOIN Patients ON Sessions.PatientId = Patients.Id
            WHERE ToneReadings.Frequency IN (3000,4000,6000) AND ReadingsSets.Ear = 0),
            ToneReadingsRelatedTablesJoinedRight AS
            (SELECT TestId, Gender, Db, TestTime, BirthDate
            FROM ToneReadings
            JOIN ReadingsSets ON ToneReadings.ReadingsSetId = ReadingsSets.Id
            JOIN Tests ON ReadingsSets.TestId = Tests.Id
            JOIN Sessions ON Tests.SessionId = Sessions.Id
            JOIN Patients ON Sessions.PatientId = Patients.Id
            WHERE ToneReadings.Frequency IN (3000,4000,6000) AND ReadingsSets.Ear = 1),
            TestTablesJoined AS
            (SELECT Tests.Id AS TestId, Tests.TestTime AS TestTime, Patients.Id AS PatientId
            FROM Tests
            JOIN Sessions ON Tests.SessionId = Sessions.Id
            JOIN Patients ON Sessions.PatientId = Patients.Id
            GROUP BY Tests.Id),
            CurrentTestsTables AS (SELECT * FROM TestTablesJoined GROUP BY TestId),
            PreviousTestsTables AS (SELECT * FROM TestTablesJoined GROUP BY TestId),

            PreviousTests AS
            (SELECT CurrentTestsTables.TestId AS TestId, PreviousTestsTables.TestId AS PreviousTestId, MAX(PreviousTestsTables.TestTime)
            FROM CurrentTestsTables, PreviousTestsTables
            WHERE CurrentTestsTables.PatientId = PreviousTestsTables.PatientId
            AND CurrentTestsTables.TestTime > PreviousTestsTables.TestTime
            AND TIMESTAMPDIFF(YEAR, PreviousTestsTables.TestTime, CurrentTestsTables.TestTime) <= 3
            GROUP BY CurrentTestsTables.TestId, PreviousTestsTables.TestId)
            
            SELECT Id AS TestId, CASE
            WHEN NOT EXISTS(SELECT 1 FROM PreviousTests WHERE PreviousTests.TestId = Tests.Id)
            THEN NULL
            WHEN (SELECT HseCategoryLeft FROM HseCategoryLeftView WHERE HseCategoryLeftView.TestId = Tests.Id)
            IS NULL THEN NULL
            WHEN (SELECT HseCategoryRight FROM HseCategoryRightView WHERE HseCategoryRightView.TestId = Tests.Id)
            IS NULL THEN NULL
            WHEN (SELECT HseCategoryLeft FROM HseCategoryLeftView WHERE HseCategoryLeftView.TestId =
                (SELECT PreviousTestId FROM PreviousTests WHERE TestId = Tests.Id))
            IS NULL THEN NULL
            WHEN (SELECT HseCategoryRight FROM HseCategoryRightView WHERE HseCategoryRightView.TestId =
                (SELECT PreviousTestId FROM PreviousTests WHERE TestId = Tests.Id))
            IS NULL THEN NULL
            WHEN
                (SELECT SUM(Db) FROM ToneReadingsRelatedTablesJoinedLeft WHERE ToneReadingsRelatedTablesJoinedLeft.TestId = Tests.Id)
                - (SELECT SUM(Db) FROM ToneReadingsRelatedTablesJoinedLeft WHERE ToneReadingsRelatedTablesJoinedLeft.TestId =
                    (SELECT PreviousTestId FROM PreviousTests WHERE TestId = Tests.Id))
                >= 30
            THEN TRUE
            WHEN
                (SELECT SUM(Db) FROM ToneReadingsRelatedTablesJoinedRight WHERE ToneReadingsRelatedTablesJoinedRight.TestId = Tests.Id)
                - (SELECT SUM(Db) FROM ToneReadingsRelatedTablesJoinedRight WHERE ToneReadingsRelatedTablesJoinedRight.TestId =
                    (SELECT PreviousTestId FROM PreviousTests WHERE TestId = Tests.Id))
                >= 30
            THEN TRUE
            ELSE FALSE
            END AS HseCategory4
            FROM Tests
            GROUP BY Tests.Id