Блог

Привет мастерам sql и волшебникам,

Я пытаюсь рассчитать дни SLA на основе определенного параметра (case-type). Для каждого типа обращения дни SLA рассчитываются немного по-разному. Мой вопрос: использую ли я ОПЕРАТОР CASE для достижения этого? Я запишу то, что у меня есть до сих пор, что не работает:

 fifthlevel --with statement declaring sla days
AS (SELECT

     CASE sla_days
     WHEN fourthlevel.case_type = 'Complaint'
     THEN sla_days =

      ( SELECT COUNT (*)
        FROM (SELECT business_date
              FROM ( SELECT TO_DATE ('01-01-2011', 'dd-mm-yyyy')   LEVEL - 1
                               business_date
                     FROM DUAL
                     CONNECT BY LEVEL <=
                                 TO_DATE ('31-12-2099', 'dd-mm-yyyy')
                               - TO_DATE ('01-01-2011', 'dd-mm-yyyy')
                                 1) date_tab1
                     WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')
                     AND business_date NOT IN (SELECT holiday_dt
                                               FROM cisadm.ci_cal_hol
                                               WHERE calendar_cd = 'WAW01'
                                              )
                   ) work_days1
             WHERE work_days1.business_date > fourthlevel.correspondence_date
             AND work_days1.business_date <= fourthlevel.close_date
             )
     WHEN fourthlevel.case_type = 'Enquiry'
     THEN sla_days = (SELECT COUNT (*)
                      FROM (SELECT business_date
                            FROM (SELECT TO_DATE ('01-01-2011',
                                                   'dd-mm-yyyy')
                                              LEVEL
                                            - 1
                                               business_date
                                   FROM DUAL
                                   CONNECT BY LEVEL <=TO_DATE('31-12-2099',
                                                                'dd-mm-yyyy')
                                                      TO_DATE('01-01-2011',
                                                              'dd-mm-yyyy')  1
                                  ) date_tab1
                            WHERE TO_CHAR (business_date, 'DY') 
                                  NOT IN ('SAT', 'SUN')
                             AND business_date 
                                  NOT IN (SELECT holiday_dt
                                          FROM cisadm.ci_cal_hol
                                          WHERE calendar_cd = 'WAW01'
                                         )
                            ) work_days1
                      WHERE work_days1.business_date > fourthlevel.agreed_Date
                        AND work_days1.business_date <=
                               fourthlevel.close_date
                     )
     END
    FROM fourthlevel,

    fourthlevel.* --also wanting to select * from preceding WITH statement
    FROM fourthlevel)
  

Хорошо, это выглядит немного запутанно, но, по сути, я пытаюсь получить два разных вычисления на основе типов case_types ‘Жалоба’ и ‘Запрос’.

Кто-нибудь может указать мне правильное направление?

Дайте мне знать, если я могу предоставить какую-либо дополнительную информацию.

Легенды!

Извлеките подзапрос work_days1 как отдельный cte:

 , work_days1 as (
SELECT business_date
       FROM (    SELECT TO_DATE ('01-01-2011', 'dd-mm-yyyy')   LEVEL - 1
                           business_date
                   FROM DUAL
             CONNECT BY LEVEL <=
                             TO_DATE ('31-12-2099', 'dd-mm-yyyy')
                           - TO_DATE ('01-01-2011', 'dd-mm-yyyy')
                             1) date_tab1
      WHERE     TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')
            AND business_date NOT IN (SELECT holiday_dt
                                        FROM cisadm.ci_cal_hol
                                       WHERE calendar_cd = 'WAW01')),
fifthlevel --with statement declaring sla days
 AS (SELECT
 CASE 
 WHEN fourthlevel.case_type = 'Complaint'
 THEN (SELECT COUNT(*) FROM work_days1 WHERE
 work_days1.business_date > fourthlevel.correspondence_date
    AND work_days1.business_date <= fourthlevel.close_date)
 WHEN fourthlevel.case_type = 'Enquiry'
 THEN sla_days = (SELECT COUNT (*) work_days1 WHERE
 work_days1.business_date > fourthlevel.agreed_Date
                    AND work_days1.business_date <=
                           fourthlevel.close_date) END as sla_days, * 
      FROM fourthlevel)
  

Кажется, что единственным различием между двумя case_type является условие work_days1.business_date . Итак, вы можете сжать запрос следующим образом:

 fifthlevel --with statement declaring sla days
AS (
SELECT
  COUNT (*) sla_days
FROM
  (
    SELECT
      business_date
    FROM
      (
        SELECT
          TO_DATE ('01-01-2011', 'dd-mm-yyyy')   LEVEL- 1 business_date
        FROM
          DUAL
          CONNECT BY LEVEL <= TO_DATE ('31-12-2099','dd-mm-yyyy') - TO_DATE ('01-01-2011','dd-mm-yyyy')   1
      )
      date_tab1
    WHERE TO_CHAR (business_date, 'DY') NOT IN ('SAT', 'SUN')
    AND business_date NOT IN (SELECT holiday_dt FROM cisadm.ci_cal_hol WHERE calendar_cd = 'WAW01')
  )
  work_days1 INNER JOIN fourthlevel
ON
  work_days1.business_date > (
    CASE
      WHEN fourthlevel.case_type = 'Complaint' THEN fourthlevel.correspondence_date
      WHEN fourthlevel.case_type = 'Enquiry' THEN fourthlevel.agreed_Date
    END)
AND work_days1.business_date <= fourthlevel.close_date;