Advertisement
ma39isy

Untitled

Apr 2nd, 2024
1,091
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     STUDENTSCHOOLYEAR,
  3.     ACTIVE,
  4.     GRADUATED,
  5.     PASSEDAWAY,
  6.     DROPOUT,
  7.     RESIGN,
  8.     UNKNOWN,
  9.     LEAVE,
  10.     NONACTIVE,
  11.     COUNT(*) AS TOTAL
  12. FROM
  13.     MASTERDATA.STUDENT
  14. JOIN MASTERDATA.STUDYPROGRAM
  15.         USING (STUDYPROGRAMID)
  16. LEFT JOIN
  17.     (
  18.     SELECT
  19.         STUDENTSCHOOLYEAR,
  20.         COUNT(*) ACTIVE
  21.     FROM
  22.         MASTERDATA.STUDENT
  23.     JOIN MASTERDATA.STUDYPROGRAM
  24.             USING (STUDYPROGRAMID)
  25.     WHERE
  26.         STUDENTTYPEID =
  27.                     '1'
  28.         AND STUDYPROGRAMID = '13'
  29.         AND TO_CHAR(STARTDATE, 'YYYY')
  30. <= '2122'
  31.         AND (TO_CHAR(OUTDATE, 'YYYY' )>
  32.     '2122'
  33.             OR OUTDATE IS NULL)
  34.     GROUP BY
  35.         (STUDENTSCHOOLYEAR))
  36.         USING (STUDENTSCHOOLYEAR)
  37. LEFT JOIN
  38.                     (
  39.     SELECT
  40.         STUDENTSCHOOLYEAR,
  41.         COUNT(*) PASSEDAWAY
  42.     FROM
  43.         MASTERDATA.STUDENT
  44.     JOIN MASTERDATA.STUDYPROGRAM
  45.             USING (STUDYPROGRAMID)
  46.     WHERE
  47.         STUDENTTYPEID =
  48.                     '5'
  49.         AND STUDYPROGRAMID = '13'
  50.         AND TO_CHAR(STARTDATE, 'YYYY')
  51.     <= '2122'
  52.             AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
  53.                 OR OUTDATE IS NULL)
  54.         GROUP BY
  55.             (STUDENTSCHOOLYEAR))
  56.         USING (STUDENTSCHOOLYEAR)
  57. LEFT JOIN (
  58.     SELECT
  59.         STUDENTSCHOOLYEAR,
  60.         COUNT(*) GRADUATED
  61.     FROM
  62.         MASTERDATA.STUDENT
  63.     JOIN MASTERDATA.STUDYPROGRAM
  64.             USING (STUDYPROGRAMID)
  65.     WHERE
  66.         STUDENTTYPEID = '2'
  67.         AND STUDYPROGRAMID = '13'
  68.         AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
  69.             AND (TO_CHAR(OUTDATE, 'YYYY') = '2122'
  70.                 OR OUTDATE IS NULL)
  71.         GROUP BY
  72.             (STUDENTSCHOOLYEAR))
  73.         USING (STUDENTSCHOOLYEAR)
  74. LEFT JOIN
  75.                     (
  76.     SELECT
  77.         STUDENTSCHOOLYEAR,
  78.         COUNT(*) DROPOUT
  79.     FROM
  80.         MASTERDATA.STUDENT
  81.     JOIN MASTERDATA.STUDYPROGRAM
  82.             USING (STUDYPROGRAMID)
  83.     WHERE
  84.         STUDENTTYPEID =
  85.                     '6'
  86.         AND STUDYPROGRAMID = '13'
  87.         AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
  88.             AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
  89.                 OR OUTDATE IS NULL)
  90.         GROUP BY
  91.             (STUDENTSCHOOLYEAR))
  92.         USING (STUDENTSCHOOLYEAR)
  93. LEFT JOIN (
  94.     SELECT
  95.         STUDENTSCHOOLYEAR,
  96.         COUNT(*) UNKNOWN
  97.     FROM
  98.         MASTERDATA.STUDENT
  99.     JOIN MASTERDATA.STUDYPROGRAM
  100.             USING (STUDYPROGRAMID)
  101.     WHERE
  102.         (STUDENTTYPEID IN ('7' , '9' , '5' )
  103.             OR STUDENTTYPEID IS NULL)
  104.         AND STUDYPROGRAMID = '13'
  105.         AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
  106.             AND (TO_CHAR(OUTDATE, 'YYYY') > '2122'
  107.                 OR OUTDATE IS NULL)
  108.         GROUP BY
  109.             (STUDENTSCHOOLYEAR))
  110.         USING (STUDENTSCHOOLYEAR)
  111. LEFT JOIN
  112.                     (
  113.     SELECT
  114.         STUDENTSCHOOLYEAR,
  115.         COUNT(*) RESIGN
  116.     FROM
  117.         MASTERDATA.STUDENT
  118.     JOIN MASTERDATA.STUDYPROGRAM
  119.             USING (STUDYPROGRAMID)
  120.     WHERE
  121.         STUDENTTYPEID =
  122.                     '3'
  123.         AND STUDYPROGRAMID = '13'
  124.         AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
  125.             AND (TO_CHAR(OUTDATE, 'YYYY' )= '2122'
  126.                 OR OUTDATE IS NULL)
  127.         GROUP BY
  128.             (STUDENTSCHOOLYEAR))
  129.         USING (STUDENTSCHOOLYEAR)
  130. LEFT JOIN (
  131.     SELECT
  132.         STUDENTSCHOOLYEAR,
  133.         COUNT(*) LEAVE
  134.     FROM
  135.         MASTERDATA.STUDENT
  136.     JOIN MASTERDATA.STUDYPROGRAM
  137.             USING (STUDYPROGRAMID)
  138.     WHERE
  139.         STUDENTTYPEID = '8'
  140.         AND STUDYPROGRAMID = '13'
  141.         AND TO_CHAR(STARTDATE, 'YYYY' )<= '2122'
  142.             AND (TO_CHAR(OUTDATE, 'YYYY') > '2122'
  143.                 OR OUTDATE IS NULL)
  144.         GROUP BY
  145.             (STUDENTSCHOOLYEAR))
  146.         USING (STUDENTSCHOOLYEAR)
  147. LEFT JOIN
  148.                     (
  149.     SELECT
  150.         STUDENTSCHOOLYEAR,
  151.         COUNT(*) NONACTIVE
  152.     FROM
  153.         MASTERDATA.STUDENT
  154.     JOIN MASTERDATA.STUDYPROGRAM
  155.             USING (STUDYPROGRAMID)
  156.     WHERE
  157.         STUDENTTYPEID IN ('10')
  158.             AND STUDYPROGRAMID = '13'
  159.             AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
  160.                 AND (TO_CHAR(OUTDATE, 'YYYY' )>
  161.         '2122'
  162.                     OR OUTDATE IS NULL)
  163.             GROUP BY
  164.                 (STUDENTSCHOOLYEAR))
  165.         USING (STUDENTSCHOOLYEAR)
  166. WHERE
  167.     1 = 1
  168.     AND STUDYPROGRAMID = '13'
  169.     AND TO_CHAR(STARTDATE, 'YYYY') <= '2122'
  170.     AND (TO_CHAR(OUTDATE, 'YYYY' )>'2122'
  171.         OR OUTDATE IS NULL)
  172. GROUP BY
  173.     STUDENTSCHOOLYEAR,
  174.     ACTIVE,
  175.     GRADUATED,
  176.     PASSEDAWAY,
  177.     DROPOUT,
  178.     RESIGN,
  179.     UNKNOWN,
  180.     LEAVE,
  181.     NONACTIVE
  182. ORDER BY
  183.     STUDENTSCHOOLYEAR
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement