Check if a value is greater than from a column

• ddicecca

  Mr or Mrs. 500

  Points: 512

  More actions

  May 21, 2009 at 1:57 pm

  #221524

  I am calculating the percentage as some of you may already know, and I would like to check to see if that percentage is equal to or greater than a certain percentage point. And then assign that as a value and then a column name just as I did with the CASE statement for "Relative Bonus Amount".

  Here is the code:

  select -- convert(varchar, delivery_date, 101) as DeliveryDate,

  -- truck_number as TruckNumber,

  -- box as Box,

  -- truck_store as TruckStore,

  -- 'Total' as Total,

  emp.fname + ' ' + emp.lname as "Full Name",

  driver as emp_cd,

  SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail) as failure_pieces,

  SUM(total_pieces) -

  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)) as success_pieces,

  SUM(total_pieces) as TotalPieces,

  convert(decimal,(SUM(total_pieces) -

  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) * 100 as success_percent,

  ROW_NUMBER() OVER(ORDER BY

  convert(decimal,(SUM(total_pieces) -

  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) desc) as "Bonus Rank",

  CASE

  WHEN ROW_NUMBER() OVER (ORDER BY convert(decimal,(SUM(total_pieces) -

  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) desc) <=2 THEN 125 WHEN ROW_NUMBER() OVER (ORDER BY convert(decimal,(SUM(total_pieces) -
  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +
  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) desc) >2 AND

  ROW_NUMBER() OVER (ORDER BY convert(decimal,(SUM(total_pieces) -

  (SUM(crm_fail_3_day) + SUM(service_fail_3_day) + SUM(not_at_home_fail) + SUM(failure_pieces) + SUM(driver_damaged_fail) + SUM(damaged_fail) + SUM(missing_harware_fail) +

  SUM(missing_parts_fail) + SUM(pref_fail) + SUM(other_fail) + SUM(does_not_fit_fail) + SUM(wrong_pull_fail) + SUM(missing_item_fail) + SUM(truck_late_fail)))) / SUM(total_pieces) desc) < 7 THEN 100 ELSE 60 END AS "Relative Bonus Amount", '' as "Individual Bonus Amount",
  '' as "Total Bonus Amount",

  'driver' as DriverType

  FROM DELIVERY_EMP_COMMISSION_RATE
  INNER JOIN EMP ON EMP.emp_cd = DELIVERY_EMP_COMMISSION_RATE.gers_id
  INNER JOIN WAREHOUSE_TRUCK_COMPLETION_STOPS ON DELIVERY_EMP_COMMISSION_RATE.gers_id = WAREHOUSE_TRUCK_COMPLETION_STOPS.driver
  WHERE DELIVERY_EMP_COMMISSION_RATE.CHM = 'C' AND
  delivery_date between '01-MAY-09' and '01-MAY-09'
  --and emp_cd = 'ATK'
  GROUP BY
  driver,
  emp.lname,
  emp.fname
  [/code]

• Jeff Moden

  SSC Guru

  Points: 1004363

  More actions

  May 21, 2009 at 7:09 pm

  #998639

  I wouldn't include any of the CASE statements in this query. Calculate all of the common equations in a CTE and then SELECT from that using the column aliases from the CTE. It'll make your code a whole lot easier to read, manage, modify, update,... etc, etc.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)