Home » SQL & PL/SQL » SQL & PL/SQL » Count function
Count function [message #221815] Wed, 28 February 2007 09:44
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have the following query that provides the desired results, but I would like to know if there is a better way to accomplish without using all of the CASE statements?

SELECT
   SUM(RES1.Total_Charting) AS Total_All_Charting,
   SUM(RES1.Total_Given) AS Total_Given,
   SUM(RES1.Total_Not_Given) AS Total_Not_Given,
   SUM(RES1.Total_Given_Barcode) AS Total_Given_Barcode,
   SUM(RES1.Total_Early_GT_60_MIN) AS Total_Early_GT_60_MIN,
   SUM(RES1.Total_Early_GT_90_MIN) AS Total_Early_GT_90_MIN,
   SUM(RES1.Total_Late_GT_60_MIN) AS Total_Late_GT_60_MIN,
   SUM(RES1.Total_Late_GT_90_MIN) AS Total_Late_GT_90_MIN,
   (SUM(RES1.BUS_Count) / SUM(RES1.Total_Charting) * 100) AS BUS_Percent,
   (SUM(RES1.ICS_Count) / SUM(RES1.Total_Charting) * 100) AS ICS_Percent,
   (SUM(RES1.CLS_Count) / SUM(RES1.Total_Charting) * 100) AS CLS_Percent,
   (SUM(RES1.BUS_RT_Count) / SUM(RES1.BUS_Count) * 100) AS BUS_RT_Percent,
   (SUM(RES1.BUS_RN_Count) / SUM(RES1.BUS_Count) * 100) AS BUS_RN_Percent,
   (SUM(RES1.ICS_RT_Count) / SUM(RES1.ICS_Count) * 100) AS ICS_RT_Percent,
   (SUM(RES1.ICS_RN_Count) / SUM(RES1.ICS_Count) * 100) AS ICS_RN_Percent,
   (SUM(RES1.CLS_RT_Count) / SUM(RES1.CLS_Count) * 100) AS CLS_RT_Percent,
   (SUM(RES1.CLS_RN_Count) / SUM(RES1.CLS_Count) * 100) AS CLS_RN_Percent
FROM
(SELECT 
   COUNT (*) Total_Charting,
   CASE
      WHEN meda.given_code = '0' 
      THEN COUNT(*)
   END Total_Given,
   CASE
      WHEN meda.given_code <> 0
      THEN COUNT(*)
   END Total_Not_Given,
   CASE 
      WHEN meda.given_code = 0 AND chart_loc LIKE '%B%'
      THEN COUNT(*)
   END Total_Given_Barcode,
   CASE
      WHEN meda.perform_ovrd = 'E'
      THEN COUNT(*)
   END Total_Early_GT_60_MIN,
   CASE
      WHEN meda.perform_ovrd = 'E' AND (sched_ddt - perform_ddt) > 5600
      THEN COUNT(*)
   END Total_Early_GT_90_MIN,
   CASE
      WHEN meda.perform_ovrd = 'L'
      THEN COUNT(*)
   END Total_Late_GT_60_MIN,
   CASE
      WHEN meda.perform_ovrd = 'L' AND (perform_ddt - sched_ddt) > 5600
      THEN COUNT(*)
   END Total_Late_GT_90_MIN,
   CASE
      WHEN meda.dept_id = 'BUS'
      THEN COUNT(*)
   END BUS_Count,
   CASE
      WHEN meda.dept_id = 'ICS'
      THEN COUNT(*)
   END ICS_Count,
   CASE
      WHEN meda.dept_id = 'CLS'
      THEN COUNT(*)
   END CLS_Count,
   CASE
      WHEN meda.dept_id = 'BUS' AND sl.title LIKE '%RT%'
      THEN COUNT(*)
   END BUS_RT_Count,
   CASE
      WHEN meda.dept_id = 'BUS' AND sl.title LIKE '%RN%'
      THEN COUNT(*)
   END BUS_RN_Count,
   CASE
      WHEN meda.dept_id = 'ICS' AND sl.title LIKE '%RT%'
      THEN COUNT(*)
   END ICS_RT_Count,
   CASE
      WHEN meda.dept_id = 'ICS' AND sl.title LIKE '%RN%'
      THEN COUNT(*)
   END ICS_RN_Count,
   CASE
      WHEN meda.dept_id = 'CLS' AND sl.title LIKE '%RT%'
      THEN COUNT(*)
   END CLS_RT_Count,
   CASE
      WHEN meda.dept_id = 'CLS' AND sl.title LIKE '%RN%'
      THEN COUNT(*)
   END CLS_RN_Count
FROM 
   meda,
   sl
WHERE 
   meda.perform_id = sl.staff_id AND
   meda.perform_ddt BETWEEN DDT.FROMDATE(TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))) + 1) AND DDT.FROMDATE((TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+((23/24)+(59/1440)))) AND
   meda.facility_id = 'S' AND 
   meda.dept_id IN ('BUS','ICS','CLS')
GROUP BY
   given_code,
   chart_loc,
   perform_ovrd,
   sched_ddt,
   perform_ddt,
   dept_id,
   title) RES1


Thanks
Stan
Previous Topic: QASPro Interface
Next Topic: Packages
Goto Forum:
  


Current Time: Wed Dec 07 14:31:54 CST 2016

Total time taken to generate the page: 0.15974 seconds