WikiStart/AdvancedReports – STMA-2324Z

Context Navigation

Version 10 (modified by 133039, 21 months ago) ( diff )
--

Напредни извештаи од базата (SQL и складирани процедури)

1. Листање(приказ) на денешни events, count на денешни часови, испити и tasks:

DECLARE correct_student_id INT;
SET correct_student_id = 11111;

WITH EventData AS (
    SELECT 
        ce.calendar_event_id,
        ce.event_name,
        ce.event_type,
        ce.event_start_time,
        ce.event_end_time,
        nre.event_date
    FROM 
        student_event AS se
    JOIN 
        calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
    JOIN 
        non_repeating_events AS nre ON ce.calendar_event_id = nre.calendar_event_id
    WHERE 
        nre.event_date = CURRENT_DATE 
        AND se.student_id = correct_student_id

    UNION

    SELECT 
        ce.calendar_event_id,
        ce.event_name,
        ce.event_type,
        ce.event_start_time,
        ce.event_end_time, 
        CASE
            WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
            WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
            WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
            WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
            WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
            WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
            WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
        END AS event_date
    FROM 
        student_event AS se
    JOIN 
        calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
    JOIN 
        repeating_events AS re ON ce.calendar_event_id = re.calendar_event_id
    WHERE 
        re.starting_date <= (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
            END
        )
        AND re.ending_date >= (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
            END
        )
        AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', (
            CASE
                WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
                WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
                WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
                WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
                WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
                WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
                WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
            END
        ))) % (7 * re.repeats_every_n_weeks)) = 0
        AND se.student_id = correct_student_id
) AS EventResult

SELECT 
    COUNT(CASE WHEN event_type = 'Exam' THEN 1 END) AS exam_count,
    COUNT(CASE WHEN event_type <> 'Exam' THEN 1 END) AS non_exam_count,
    COUNT(task.task_id) AS task_count
FROM EventResult
LEFT JOIN task ON EventResult.event_date = task.task_date;

2. Листање(приказ) на таскови за наредните три дена:

DECLARE correct_student_id INT;
SET correct_student_id = 11111;
SELECT t.*
FROM task t
JOIN dashboard_task dt ON t.task_id = dt.task_id
JOIN dashboard d ON dt.student_id = d.student_id
JOIN calendar_event ce ON d.dashboard_date = ce.event_start_time::date
WHERE d.student_id = chosen_student_id
 AND ce.event_start_time BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '3 days';

3. Листање(приказ) на испити за наредните седум дена:

4. Листање(приказ) на сите евенти за одредена недела:

DECLARE correct_student_id INT;
SET correct_student_id = 11111;

SELECT *
FROM (
  SELECT 
    ce.calendar_event_id,
    ce.event_name,
    ce.event_type,
    ce.event_start_time,
    ce.event_end_time,
    nre.event_date
  FROM 
    student_event AS se
  JOIN 
    calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
  JOIN 
    non_repeating_events AS nre ON ce.calendar_event_id = nre.calendar_event_id
  WHERE 
    nre.event_date >= date_trunc('week', CURRENT_DATE) 
    AND nre.event_date < date_trunc('week', CURRENT_DATE) + INTERVAL '1 week'
    AND se.student_id = correct_student_id

  UNION

  SELECT 
    ce.calendar_event_id,
    ce.event_name,
    ce.event_type,
    ce.event_start_time,
    ce.event_end_time, 
    CASE
      WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
      WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
      WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
      WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
      WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
      WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
      WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
    END AS event_date
  FROM 
    student_event AS se
  JOIN 
    calendar_event AS ce ON se.calendar_event_id = ce.calendar_event_id
  JOIN 
    repeating_events AS re ON ce.calendar_event_id = re.calendar_event_id
  WHERE 
    re.starting_date <= (
      CASE
        WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
        WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
        WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
        WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
        WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
        WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
        WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
      END
    )
    AND re.ending_date >= (
      CASE
        WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
        WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
        WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
        WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
        WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
        WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
        WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
      END
    )
    AND (EXTRACT(day FROM date_trunc('week', CURRENT_DATE) - date_trunc('week', (
        CASE
          WHEN re.event_week_day = 'MONDAY' THEN date_trunc('week', CURRENT_DATE)::DATE
          WHEN re.event_week_day = 'TUESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '1 day'::DATE
          WHEN re.event_week_day = 'WEDNESDAY' THEN date_trunc('week', CURRENT_DATE) + interval '2 days'::DATE
          WHEN re.event_week_day = 'THURSDAY' THEN date_trunc('week', CURRENT_DATE) + interval '3 days'::DATE
          WHEN re.event_week_day = 'FRIDAY' THEN date_trunc('week', CURRENT_DATE) + interval '4 days'::DATE
          WHEN re.event_week_day = 'SATURDAY' THEN date_trunc('week', CURRENT_DATE) + interval '5 days'::DATE
          WHEN re.event_week_day = 'SUNDAY' THEN date_trunc('week', CURRENT_DATE) + interval '6 days'::DATE
        END
      ))) % (7 * re.repeats_every_n_weeks)) = 0
    AND se.student_id = correct_student_id
) AS Result
ORDER BY 
  event_date, -- Order by day of the week
  event_start_time; -- Order by time

5. Листање(приказ) на сите евенти за одреден семестар:

6. Листање(приказ) на сите евенти и task-ови за зададен предмет:

7. Листање(приказ) на сите на сите книги и автори по одреден предмет (пример:Бази на податоци):

DECLARE correct_student_id INT;
SET correct_student_id = 11111;

WITH SubjectBooks AS (
    SELECT 
        rb.book_id,
        rb.book_title,
        ba.author_id,
        a.author_name
    FROM 
        recommended_book AS rb
    JOIN 
        book_author AS ba ON rb.book_id = ba.book_id
    WHERE 
        rb.subject_name = 'Bazi na podatoci'
        AND rb.student_id = correct_student_id
)

SELECT 
    sb.book_id,
    sb.book_title,
    sb.author_id,
    sb.author_name
FROM 
    SubjectBooks AS sb;

8. Приказна на сите feedbacks (описи и наслови на feedback) со оценка помала од 3:

DECLARE correct_student_id INT;
SET correct_student_id = 11111;

SELECT 
    fb.fb_id,
    fb.fb_date,
    fb.fb_title,
    fb.fb_description,
    fb.fb_rating
FROM 
    feedback AS fb
WHERE 
    fb.fb_rating < 3
    AND EXISTS (
        SELECT 1
        FROM 
            gives_feedback AS gf
        WHERE 
            gf.fb_id = fb.fb_id
            AND gf.student_id = correct_student_id
    );

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text