mardi 15 mars 2016

Retrieve value for Temporary column based on the value of a column in sub query

I have a SQL Query as below :

SELECT SUM(Z.MaxMarks) AS TotMaxMarks,
    SUM(Z.MinMarks) AS TotMinMarks,
    SUM(Z.SecuredMarks) AS TotSecuredMarks,
    Z.IsPass
FROM (
    SELECT Y.*
    FROM (
        SELECT X.MaxMarks,
            X.SecuredMarks,
            (X.MaxMarks * X.PassPercentage / 100) AS MinMarks,
            CASE WHEN ((X.MaxMarks * X.PassPercentage / 100) < X.SecuredMarks) THEN 'True' ELSE 'False' END AS IsPass,
            row_number() OVER (
                PARTITION BY ChapterID ORDER BY UpdatedDate DESC
                ) AS SequencNo
        FROM (
            SELECT C.ChapterID,
                T.MinimumMarks AS PassPercentage,
                (
                    SELECT Sum(Q.MaximumMarks)
                    FROM MstQuestion Q,
                        TransStudentAnswer SA
                    WHERE Q.QuestionID = SA.QuestionID AND SA.TestAllotmentID = TA.TestAllotmentID
                    ) AS MaxMarks,
                (
                    SELECT Sum(Score)
                    FROM TransStudentAnswer
                    WHERE TestAllotmentID = TA.TestAllotmentID
                    ) AS SecuredMarks,
                TA.UpdatedDate
            FROM TransTestAllotment TA,
                MstStudent S,
                MstBatchDetails B,
                MstTest T,
                MstChapter C
            WHERE TA.StudentID = 47 AND TA.BatchID = 10 AND T.TestID = TA.TestID AND S.StudentID = TA.StudentID AND B.BatchID = TA.BatchID AND T.ChapterID = C.ChapterID AND TA.IsAttempted = 'True' AND TA.IsEvaluated = 'True'
            ) X
        ) Y
    WHERE SequencNo = 1
    ) Z
GROUP BY Z.IsPass

It is returning results as below :

+-------------+-------------+-----------------+--------+
| TotMaxMarks | TotMinMarks | TotSecuredMarks | IsPass |
+-------------+-------------+-----------------+--------+
|          20 |          10 |               3 | False  |
|          30 |          15 |              28 | True   |
+-------------+-------------+-----------------+--------+

Is it possible to modify this query such that it returns one record with SUM(Z.MaxMarks),SUM(Z.MinMarks),SUM(Z.SecuredMarks) and TempIsPass column whose value will be true if value of the column Z.Ispass is true in all records of Z, else it should be false.

Aucun commentaire:

Enregistrer un commentaire