title: Query optimization needed
body: <p>Need to optimize my sql query to enhance the performance. below is the query</p>
  
  <pre><code> SELECT 
        (
        CASE
          WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
            FROM PREFS
            WHERE PREFS.SCHOOLID  = EV.SCHOOLID
            AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
            AND PREFS.YEARID      = EV.YEARID
            AND PREFS.DCID        =
              (SELECT MIN(DCID)
              FROM PREFS PDUPS
              WHERE PDUPS.SCHOOLID  = EV.SCHOOLID
              AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
              AND PDUPS.YEARID      = EV.YEARID
              ) )                   = '1'
          THEN
            (SELECT COUNT(PER.PERIOD_NUMBER)
            FROM PERIOD PER,
              BELL_SCHEDULE_ITEMS BSI,
              CYCLE_DAY CY,
              CALENDAR_DAY CDSUB,
              CC CC,
              SECTION_MEETING SM,
              SECTIONS SEC
            WHERE CC.DATEENROLLED   &lt;= CD.DATE_VALUE
            AND CD.DATE_VALUE        &lt; CC.DATELEFT
            AND ABS( CC.SECTIONID )  = SM.SECTIONID
            AND SM.SECTIONID         = SEC.ID
            AND SEC.EXCLUDE_ADA      = 0
            AND SM.PERIOD_NUMBER     = PER.PERIOD_NUMBER
            AND SM.SCHOOLID          = PER.SCHOOLID
            AND SM.YEAR_ID           = PER.YEAR_ID
            AND BSI.PERIOD_ID        = PER.ID
            AND BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
            AND ( (CDSUB.SCHOOLID    = EV.SCHOOLID
            AND BSI.ADA_CODE         = 1)
            OR (CDSUB.SCHOOLID      &lt;&gt; EV.SCHOOLID) )
            AND SM.CYCLE_DAY_LETTER  = CY.LETTER
            AND SM.SCHOOLID          = CY.SCHOOLID
            AND SM.YEAR_ID           = CY.YEAR_ID
            AND SM.YEAR_ID           = EV.YEARID
            AND CY.ID                = CDSUB.CYCLE_DAY_ID
            AND CDSUB.DATE_VALUE     = CD.DATE_VALUE
            AND CDSUB.INSESSION      = 1
            AND CC.STUDENTID         = EV.STUDENTID
            AND CC.DCID NOT         IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            )
          ELSE
            (SELECT COUNT(P.PERIOD_NUMBER)
            FROM CC CC,
              SECTION_MEETING SM,
              CYCLE_DAY CY,
              PERIOD P,
              BELL_SCHEDULE_ITEMS BSI,
              SECTIONS SEC
            WHERE CC.STUDENTID       = EV.STUDENTID
            AND CC.DATEENROLLED     &lt;= CD.DATE_VALUE
            AND CC.DATELEFT          &gt; CD.DATE_VALUE
            AND CY.ID                = CD.CYCLE_DAY_ID
            AND SM.SECTIONID         = ABS(CC.SECTIONID)
            AND SM.CYCLE_DAY_LETTER  = CY.LETTER
            AND SM.PERIOD_NUMBER     = P.PERIOD_NUMBER
            AND SM.SCHOOLID          = P.SCHOOLID
            AND SM.YEAR_ID           = P.YEAR_ID
            AND SM.YEAR_ID           = EV.YEARID
            AND SM.SECTIONID         = SEC.ID
            AND SEC.EXCLUDE_ADA      = 0
            AND BSI.BELL_SCHEDULE_ID = CD.BELL_SCHEDULE_ID
            AND BSI.ADA_CODE         = 1
            AND BSI.PERIOD_ID        = P.ID
            AND CC.DCID NOT         IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            )
        END) AS POTENTIAL_PERIODS_PRESENT,
        (
        CASE
          WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
            FROM PREFS
            WHERE PREFS.SCHOOLID  = EV.SCHOOLID
            AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
            AND PREFS.YEARID      = EV.YEARID
            AND PREFS.DCID        =
              (SELECT MIN(DCID)
              FROM PREFS PDUPS
              WHERE PDUPS.SCHOOLID  = EV.SCHOOLID
              AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
              AND PDUPS.YEARID      = EV.YEARID
              ) )                   = '1'
          THEN NVL(
            (SELECT SUM(BSI.MINUTES_ATTENDED)
            FROM PERIOD PER,
              BELL_SCHEDULE_ITEMS BSI,
              CYCLE_DAY CY,
              CALENDAR_DAY CDSUB,
              CC CC,
              SECTION_MEETING SM,
              SECTIONS SEC
            WHERE CC.DATEENROLLED   &lt;= CD.DATE_VALUE
            AND CD.DATE_VALUE        &lt; CC.DATELEFT
            AND ABS( CC.SECTIONID )  = SM.SECTIONID
            AND SM.SECTIONID         = SEC.ID
            AND SEC.EXCLUDE_ADA      = 0
            AND SM.PERIOD_NUMBER     = PER.PERIOD_NUMBER
            AND SM.SCHOOLID          = PER.SCHOOLID
            AND SM.YEAR_ID           = PER.YEAR_ID
            AND BSI.PERIOD_ID        = PER.ID
            AND BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
            AND ( (CDSUB.SCHOOLID    = EV.SCHOOLID
            AND BSI.ADA_CODE         = 1)
            OR (CDSUB.SCHOOLID      &lt;&gt; EV.SCHOOLID) )
            AND SM.CYCLE_DAY_LETTER  = CY.LETTER
            AND SM.SCHOOLID          = CY.SCHOOLID
            AND SM.YEAR_ID           = CY.YEAR_ID
            AND SM.YEAR_ID           = EV.YEARID
            AND CY.ID                = CDSUB.CYCLE_DAY_ID
            AND CDSUB.DATE_VALUE     = CD.DATE_VALUE
            AND CDSUB.INSESSION      = 1
            AND CC.STUDENTID         = EV.STUDENTID
            AND CC.DCID NOT         IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            ) ,0)
          ELSE NVL(
            (SELECT SUM(BSI.MINUTES_ATTENDED)
            FROM CC CC,
              SECTION_MEETING SM,
              CYCLE_DAY CY,
              PERIOD P,
              BELL_SCHEDULE_ITEMS BSI,
              SECTIONS SEC
            WHERE CC.STUDENTID       = EV.STUDENTID
            AND CC.DATEENROLLED     &lt;= CD.DATE_VALUE
            AND CC.DATELEFT          &gt; CD.DATE_VALUE
            AND CY.ID                = CD.CYCLE_DAY_ID
            AND SM.SECTIONID         = ABS(CC.SECTIONID)
            AND SM.CYCLE_DAY_LETTER  = CY.LETTER
            AND SM.PERIOD_NUMBER     = P.PERIOD_NUMBER
            AND SM.SCHOOLID          = P.SCHOOLID
            AND SM.YEAR_ID           = P.YEAR_ID
            AND SM.YEAR_ID           = EV.YEARID
            AND SM.SECTIONID         = SEC.ID
            AND SEC.EXCLUDE_ADA      = 0
            AND BSI.BELL_SCHEDULE_ID = CD.BELL_SCHEDULE_ID
            AND BSI.ADA_CODE         = 1
            AND BSI.PERIOD_ID        = P.ID
            AND CC.DCID NOT         IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            ) ,0)
        END) AS POTENTIAL_MINUTES_PRESENT,
        (
        CASE
          WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
            FROM PREFS
            WHERE PREFS.SCHOOLID  = EV.SCHOOLID
            AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
            AND PREFS.YEARID      = EV.YEARID
            AND PREFS.DCID        =
              (SELECT MIN(DCID)
              FROM PREFS PDUPS
              WHERE PDUPS.SCHOOLID  = EV.SCHOOLID
              AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
              AND PDUPS.YEARID      = EV.YEARID
              ) )                   = '1'
          THEN
            (SELECT COUNT(*)
            FROM ATTENDANCE ATT,
              ATTENDANCE_CODE AC,
              CC CC,
              SECTIONS SEC,
              BELL_SCHEDULE_ITEMS BSI,
              CALENDAR_DAY CDSUB
            WHERE ATT.STUDENTID        = CC.STUDENTID
            AND CDSUB.DATE_VALUE       = CD.DATE_VALUE
            AND CDSUB.BELL_SCHEDULE_ID = BSI.BELL_SCHEDULE_ID
            AND BSI.PERIOD_ID          = ATT.PERIODID
            AND ( (CDSUB.SCHOOLID      = EV.SCHOOLID
            AND BSI.ADA_CODE           = 1)
            OR (CDSUB.SCHOOLID        &lt;&gt; EV.SCHOOLID) )
            AND ATT.CCID               = CC.ID
            AND ATT.ATTENDANCE_CODEID  = AC.ID
            AND CC.DATEENROLLED       &lt;= ATT.ATT_DATE
            AND ATT.ATT_DATE           &lt; CC.DATELEFT
            AND ATT.ATT_MODE_CODE      = 'ATT_ModeMeeting'
            AND AC.PRESENCE_STATUS_CD  = 'Absent'
            AND AC.CALCULATE_ADA_YN    = 1
            AND ABS(CC.SECTIONID)      = SEC.ID
            AND SEC.EXCLUDE_ADA        = 0
            AND ATT.STUDENTID          = EV.STUDENTID
            AND ATT.ATT_DATE           = CD.DATE_VALUE
            AND CC.DCID NOT           IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            )
          ELSE
            (SELECT COUNT(*)
            FROM ATTENDANCE ATT,
              ATTENDANCE_CODE AC,
              CC CC,
              SECTIONS SEC,
              BELL_SCHEDULE_ITEMS BSI
            WHERE ATT.STUDENTID       = CC.STUDENTID
            AND CD.BELL_SCHEDULE_ID   = BSI.BELL_SCHEDULE_ID
            AND BSI.PERIOD_ID         = ATT.PERIODID
            AND BSI.ADA_CODE          = 1
            AND ATT.CCID              = CC.ID
            AND ATT.ATTENDANCE_CODEID = AC.ID
            AND ATT.ATT_DATE         &gt;= CC.DATEENROLLED
            AND ATT.ATT_DATE          &lt; CC.DATELEFT
            AND ATT.ATT_MODE_CODE     = 'ATT_ModeMeeting'
            AND AC.PRESENCE_STATUS_CD = 'Absent'
            AND AC.CALCULATE_ADA_YN   = 1
            AND ABS(CC.SECTIONID)     = SEC.ID
            AND SEC.EXCLUDE_ADA       = 0
            AND ATT.STUDENTID         = EV.STUDENTID
            AND ATT.ATT_DATE          = CD.DATE_VALUE
            AND CC.DCID NOT          IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            )
        END) AS PERIODS_ABSENT,
        (
        CASE
          WHEN (SELECT NVL(TO_CHAR(PREFS.VALUE), '0')
            FROM PREFS
            WHERE PREFS.SCHOOLID  = EV.SCHOOLID
            AND LOWER(PREFS.NAME) = 'att_calccntattothschenrthissch'
            AND PREFS.YEARID      = EV.YEARID
            AND PREFS.DCID        =
              (SELECT MIN(DCID)
              FROM PREFS PDUPS
              WHERE PDUPS.SCHOOLID  = EV.SCHOOLID
              AND LOWER(PDUPS.NAME) = 'att_calccntattothschenrthissch'
              AND PDUPS.YEARID      = EV.YEARID
              ) )                   = '1'
          THEN NVL(
            (SELECT SUM(
              CASE
                WHEN NVL(ATTT.ENTRYCOUNT, 0) = 0
                THEN NVL( (
                  CASE
                    WHEN AC.PRESENCE_STATUS_CD = 'Absent'
                    AND AC.CALCULATE_ADA_YN    = 1
                    THEN BSI.MINUTES_ATTENDED
                    WHEN AC.PRESENCE_STATUS_CD = 'Absent'
                    AND AC.CALCULATE_ADA_YN    = 0
                    THEN 0
                    ELSE BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES
                  END), 0)
                ELSE NVL(BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES, 0)
              END)
            FROM ATTENDANCE ATT
            JOIN ATTENDANCE_CODE AC
            ON ATT.ATTENDANCE_CODEID = AC.ID
            JOIN CC CC
            ON ATT.CCID = CC.ID
            JOIN SECTIONS SEC
            ON ABS(CC.SECTIONID) = SEC.ID
            JOIN BELL_SCHEDULE_ITEMS BSI
            ON ATT.PERIODID = BSI.PERIOD_ID
            JOIN CALENDAR_DAY CDSUB
            ON BSI.BELL_SCHEDULE_ID = CDSUB.BELL_SCHEDULE_ID
            LEFT JOIN
              (SELECT ATTT2.ATTENDANCEID,
                COUNT(ATTT2.ATTENDANCEID) ENTRYCOUNT
              FROM ATTENDANCE_TIME ATTT2
              GROUP BY ATTT2.ATTENDANCEID
              ) ATTT
            ON ATT.ID             = ATTT.ATTENDANCEID
            WHERE ATT.STUDENTID   = CC.STUDENTID
            AND CDSUB.DATE_VALUE  = CD.DATE_VALUE
            AND ( (CDSUB.SCHOOLID = EV.SCHOOLID
            AND BSI.ADA_CODE      = 1)
            OR (CDSUB.SCHOOLID   &lt;&gt; EV.SCHOOLID) )
            AND CC.DATEENROLLED  &lt;= ATT.ATT_DATE
            AND ATT.ATT_DATE      &lt; CC.DATELEFT
            AND ATT.ATT_MODE_CODE = 'ATT_ModeMeeting'
            AND SEC.EXCLUDE_ADA   = 0
            AND ATT.STUDENTID     = EV.STUDENTID
            AND ATT.ATT_DATE      = CD.DATE_VALUE
            AND CC.DCID NOT      IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            ) ,0)
          ELSE NVL(
            (SELECT SUM(
              CASE
                WHEN NVL(ATTT.ENTRYCOUNT, 0) = 0
                THEN NVL( (
                  CASE
                    WHEN AC.PRESENCE_STATUS_CD = 'Absent'
                    AND AC.CALCULATE_ADA_YN    = 1
                    THEN BSI.MINUTES_ATTENDED
                    WHEN AC.PRESENCE_STATUS_CD = 'Absent'
                    AND AC.CALCULATE_ADA_YN    = 0
                    THEN 0
                    ELSE BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES
                  END), 0)
                ELSE NVL(BSI.MINUTES_ATTENDED - ATT.TOTAL_MINUTES, 0)
              END)
            FROM ATTENDANCE ATT
            JOIN ATTENDANCE_CODE AC
            ON ATT.ATTENDANCE_CODEID = AC.ID
            JOIN CC CC
            ON ATT.CCID = CC.ID
            JOIN BELL_SCHEDULE_ITEMS BSI
            ON BSI.PERIOD_ID = ATT.PERIODID
            JOIN SECTIONS SEC
            ON ABS(CC.SECTIONID) = SEC.ID
            LEFT JOIN
              (SELECT ATTT2.ATTENDANCEID,
                COUNT(ATTT2.ATTENDANCEID) ENTRYCOUNT
              FROM ATTENDANCE_TIME ATTT2
              GROUP BY ATTT2.ATTENDANCEID
              ) ATTT
            ON ATT.ID               = ATTT.ATTENDANCEID
            WHERE ATT.STUDENTID     = CC.STUDENTID
            AND CD.BELL_SCHEDULE_ID = BSI.BELL_SCHEDULE_ID
            AND ATT.ATT_DATE       &gt;= CC.DATEENROLLED
            AND ATT.ATT_DATE        &lt; CC.DATELEFT
            AND ATT.ATT_MODE_CODE   = 'ATT_ModeMeeting'
            AND SEC.EXCLUDE_ADA     = 0
            AND BSI.ADA_CODE        = 1
            AND ATT.STUDENTID       = EV.STUDENTID
            AND ATT.ATT_DATE        = CD.DATE_VALUE
            AND CC.DCID NOT        IN
              (SELECT CCE.CCDCID
              FROM CC_EXCLUSIONS CCE
              WHERE CD.DATE_VALUE&gt;=CCE.STARTDATE
              AND CD.DATE_VALUE  &lt;=CCE.ENDDATE
              )
            ) ,0)
        END) AS MINUTES_ABSENT
      FROM PT_ENROLLMENT_ALL EV,
        CALENDAR_DAY CD,
        BELL_SCHEDULE BS
      WHERE EV.SCHOOLID       = CD.SCHOOLID
      AND EV.SCHOOLID         = 2053
      AND CD.BELL_SCHEDULE_ID = BS.ID
      AND CD.INSESSION        = 1
      AND CD.DATE_VALUE      &gt;= EV.ENTRYDATE
      AND CD.DATE_VALUE       &lt; EV.EXITDATE
      AND CD.DATE_VALUE      &gt;= to_date('07/28/2018', 'mm/dd/yyyy')
      AND CD.DATE_VALUE      &lt;= to_date('06/30/2019', 'mm/dd/yyyy');
  </code></pre>
  
  <p>When I tried to use With clause for duplicate queries then the performance goes increased stead of reducing. Thanks for your help in advance. So for these listed columns you can see there are multiple case statements are there and few chunks of codes are repeated as well. Is there any way that can be optimized with except clause.</p>