Breaking News: Grepper is joining You.com. Read the official announcement!

sql optimised statistics operations code

Bartosz Waszczyk answered on February 5, 2024 Popularity 6/10 Helpfulness 2/10

answer sql optimised statistics operations code

sql optimised statistics operations code

Comment

SELECT j.id,
       NVL2(ssv.name, ssv.name, odv.name) AS object_name,
       j.created_date,
       issue_from,
       CONCAT(CONCAT(su1.first_name, ' '), su1.last_name) AS created_by_name,
       CONCAT(CONCAT(su2.first_name, ' '), su2.last_name) AS assigned_by_name,
       j.customer_case,
       j.issue_type_id,
       CASE
           WHEN SUBSTR(t3.ccc, 1, 4000) LIKE '%Ticket is created%' THEN 'Open'
           WHEN SUBSTR(t3.ccc, 1, 4000) LIKE 'From - to %' THEN 'Assigned'
           ELSE SUBSTR(t3.ccc, 1, 4000)
       END AS status,
       t3.created_date
FROM jira_mini j
JOIN (
    SELECT jma.jira_mini_id,
           SUBSTR(jma.action_content, 1, 4000) AS ccc,
           jma.created_date,
           jma.action_type_id
    FROM jira_mini_action jma
    WHERE jma.created_date >= TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS')
      AND jma.created_date < ADD_MONTHS(TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS'), 1)
      AND (jma.action_type_id IN (1, 5))
      AND jma.jira_mini_id IN (
          SELECT jm.id
          FROM jira_mini jm
          WHERE (jm.issue_from LIKE 'APEX500'
                 OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL))
            AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
      )
    UNION
    SELECT a.jira_mini_id,
           SUBSTR(a.action_content, 1, 4000) AS ccc,
           a.created_date,
           a.action_type_id
    FROM jira_mini_action a
    WHERE a.created_date = (
        SELECT MAX(b.created_date)
        FROM jira_mini_action b
        WHERE (b.action_type_id IN (1, 5))
          AND b.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS')
          AND a.jira_mini_id = b.jira_mini_id
    )
      AND (a.action_type_id IN (1, 5))
      AND SUBSTR(a.action_content, 1, 4000) <> 'Close'
      AND a.jira_mini_id NOT IN (
          SELECT jm.id
          FROM data_management.jira_mini jm
          JOIN (
              SELECT jma.jira_mini_id, MAX(jma.created_date) AS max2
              FROM data_management.jira_mini_action jma
              WHERE jma.action_type_id = 1
                AND SUBSTR(jma.action_content, 1, 4000) = 'Close'
                AND jma.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS')
              GROUP BY jma.jira_mini_id
          ) t2 ON jm.id = t2.jira_mini_id
          WHERE jm.assigned_by IS NOT NULL
            AND jm.issue_status_id = 8
            AND jm.created_date < TO_DATE(report_date, 'yyyy-mm-dd HH24:MI:SS')
      )
      AND a.jira_mini_id IN (
          SELECT jm.id
          FROM jira_mini jm
          WHERE (jm.issue_from LIKE 'APEX500'
                 OR (jm.issue_from NOT LIKE 'APEX500' AND jm.assigned_by IS NOT NULL))
            AND jm.created_date >= TO_DATE('2016-01-01 00:00:00', 'yyyy-mm-dd HH24:MI:SS')
      )
) t3 ON j.id = t3.jira_mini_id
LEFT JOIN sources.series_dataset_v ssv ON ssv.id = j.object_id
LEFT JOIN sources.outage_dataset_v odv ON odv.id = j.object_id
LEFT JOIN safe_user su1 ON su1.id = j.created_by
LEFT JOIN safe_user su2 ON su2.id = j.assigned_by
ORDER BY j.id, t3.created_date ASC;

Popularity 6/10 Helpfulness 2/10 Language sql

Source: chat.openai.com

Tags: operations sql statistics

Link to this answer
Share Copy Link

Contributed on Feb 05 2024

Bartosz Waszczyk

0 Answers Avg Quality 2/10

sql optimised statistics operations code

Contents

More Related Answers

sql optimised statistics operations code

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.