Блог

Добрый день — Извините за глупый вопрос, я, кажется, не могу понять, как ввести оператор WHERE для вычисления, чтобы возвращать только отрицательные значения REV_DIFF ( (SUM(PREVIOUS_REVENUE) — СУММА(CURRENT_REVENUE)) REV_DIFF). Пожалуйста, простите за форматирование — все еще учусь… Спасибо!

 WITH X AS
(
  SELECT
    1 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME, T.BILL_NUMBER CURRENT_FB, T.WEIGHT CURRENT_WEIGHT,
    (T.CHARGES   T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END CURRENT_REVENUE,
    NULL PREVIOUS_FB, NULL PREVIOUS_WEIGHT, NULL PREVIOUS_REVENUE  
  FROM TLORDER T
   INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
   INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
  WHERE 1 = 1
    AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
    AND T.EXTRA_STOPS <> 'Child'
    AND T.BILL_NUMBER NOT IN ('0', 'NA')
    AND I.COMPANY_INFO_ID = 3
    AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 90 days AND CURRENT TIMESTAMP  -60 days
 
  UNION ALL

  SELECT
    2 TYPE, I.NAME COMPANY, C.CLIENT_ID, C.NAME CLIENT_NAME, NULL CURRENT_FB, NULL CURRENT_WEIGHT, NULL CURRENT_REVENUE, T.BILL_NUMBER PREVIOUS_FB, T.WEIGHT PREVIOUS_WEIGHT,
    (T.CHARGES   T.XCHARGES) * CASE WHEN T.TOTAL_CHARGES = 0 THEN 0 ELSE (T.FUNCTIONAL_AMT / T.TOTAL_CHARGES) END PREVIOUS_REVENUE
  FROM TLORDER T
   INNER JOIN CLIENT C ON C.CLIENT_ID = T.BILL_TO_CODE
   INNER JOIN COMPANY_INFO_SRC I ON I.COMPANY_INFO_ID = T.COMPANY_ID
  WHERE 1 = 1
    AND COALESCE(T.INTERFACE_STATUS_F,0) <> -1
    AND T.EXTRA_STOPS <> 'Child'
    AND T.BILL_NUMBER NOT IN ('0', 'NA')
    AND I.COMPANY_INFO_ID = 3
    AND DATE(T.PICK_UP_BY) BETWEEN CURRENT TIMESTAMP - 60 days AND CURRENT TIMESTAMP
 )

SELECT
  1 SORT, COMPANY, CLIENT_ID, CLIENT_NAME, 
  COUNT(DISTINCT CURRENT_FB) CURRENT_FB,  COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB, 
  ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
  SUM(CURRENT_WEIGHT) CURRENT_WEIGHT,  SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
  ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
  SUM(CURRENT_REVENUE) CURRENT_REVENUE,  SUM(PREVIOUS_REVENUE) PREVIOUS_REVENUE,
  ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2) REVENUE_DIFF,
  (SUM(PREVIOUS_REVENUE) - SUM(CURRENT_REVENUE)) REV_DIFF
FROM X 
GROUP BY COMPANY, CLIENT_ID, CLIENT_NAME

UNION ALL

SELECT
  2 SORT, COMPANY, 'TOTAL' CLIENT_ID, NULL CLIENT_NAME,
  COUNT(DISTINCT CURRENT_FB) CURRENT_FB,  COUNT(DISTINCT PREVIOUS_FB) PREVIOUS_FB, 
  ROUND(CASE WHEN COUNT(PREVIOUS_FB) <> 0 THEN (COUNT(CURRENT_FB) - COUNT(PREVIOUS_FB)) / CAST(COUNT(PREVIOUS_FB) AS DOUBLE) * 100.00 ELSE 0 END, 2) FB_DIFF,
  SUM(CURRENT_WEIGHT) CURRENT_WEIGHT,  SUM(PREVIOUS_WEIGHT) PREVIOUS_WEIGHT,
  ROUND(CASE WHEN SUM(PREVIOUS_WEIGHT) <> 0 THEN (SUM(CURRENT_WEIGHT) - SUM(PREVIOUS_WEIGHT)) / SUM(PREVIOUS_WEIGHT) * 100.00 ELSE 0 END, 2) WEIGHT_DIFF,
  SUM(CURRENT_REVENUE) CURRENT_REVENUE,  SUM(PREVIOUS_REVENUE) PREVIOUS_REVENUE,
  ROUND(CASE WHEN SUM(PREVIOUS_REVENUE) <> 0 THEN (SUM(CURRENT_REVENUE) - SUM(PREVIOUS_REVENUE)) / SUM(PREVIOUS_REVENUE) * 100.00 ELSE 0 END, 2)  REVENUE_DIFF,
  (SUM(PREVIOUS_REVENUE) - SUM(CURRENT_REVENUE)) REV_DIFF
FROM X
GROUP BY COMPANY

ORDER BY REV_DIFF
 

Вы можете использовать HAING предложение вместе с GROUP BY для фильтрации записей, имеющих отрицательные значения. После GROUP BY использования этого.

 HAVING (SUM(PREVIOUS_REVENUE) - SUM(CURRENT_REVENUE))) < 0