Link to home
Create AccountLog in
Avatar of Wirt
WirtFlag for United States of America

asked on

Need Help getting the Average of a COL

I'm creating a Table from a single Query and I'm having trouble with getting the Avg to calulate correctly.

--Avg Cycle
       AVG(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
             AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
             AND STEP_NAME = 'Order Info Provided To AE'
THEN
DATEDIFF(DAY,PROJECT_ACTIVITY.PROJECT_RECD_DT,PMT_STEP_PROCESS_CYCLE.STEP_COMPLETED_DT)
                        ELSE 0
                        END) AS AVG
<cffunction name="getPostTrainingData">
	<cfargument name="date_1">
	<cfargument name="date_2">
<cfquery name="getTrainingData" datasource="#dbname#" username="#dbuser#" password="#dbpw#">
SELECT 	DIR,PRODUCT, 
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'			AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
	THEN 1 ELSE 0 END) AS PRODS_REC,
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'
	 AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
	 THEN PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_QTY 
          ELSE 0 END) AS QTY_REC,
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'
	 AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
	 THEN PMT_PRODUCT_PROCESS_CYCLE.MRC
	 ELSE 0	 END) AS REV_REC,
--Comp Acts
SUM(CASE WHEN PMT_STEP_PROCESS_CYCLE.STEP_NAME = 'Order Info Provided To AE'     
	 AND STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
	 AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'	
	THEN 1  ELSE 0 END) AS COMP_ACTS,
--Comp Qty	
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
	 AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
	 THEN PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_QTY
	 ELSE 0 END)AS COMP_QTY,
--Comp Rev
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
	 AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
	THEN PMT_PRODUCT_PROCESS_CYCLE.MRC 
         ELSE 0  END)AS COMP_REV,
--Avg Cycle
AVG(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
	 AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
          AND STEP_NAME = 'Order Info Provided To AE'
THEN 
DATEDIFF(DAY,PROJECT_ACTIVITY.PROJECT_RECD_DT,PMT_STEP_PROCESS_CYCLE.STEP_COMPLETED_DT)
ELSE 0 END) AS AVG
 
FROM PMT_PRODUCT_PROCESS_CYCLE JOIN PMT_STEP_PROCESS_CYCLE
	 ON PMT_PRODUCT_PROCESS_CYCLE.PROJECT_ID = PMT_STEP_PROCESS_CYCLE.PROJECT_ID
	 JOIN PMT_PRODUCTS
	 ON PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_TYPE = PMT_PRODUCTS.PRODUCT 
	 JOIN PROJECT_ACTIVITY
	 ON PMT_PRODUCT_PROCESS_CYCLE.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
	 JOIN PMT_USERS
	 ON (PROJECT_ACTIVITY.PMT_USER_ID = PMT_USERS.PMT_USER_ID
		 OR PROJECT_ACTIVITY.CENT_OWNER = PMT_USERS.PMT_USER_ID)
WHERE DIR <> '0'
		AND ADSS <> '0'
		AND USER_ROLE = 'CSS'
		AND PRODUCT_SET = '#prodSet#'
		AND TRAINING_CLASS = '#className#'
 
GROUP BY DIR,PRODUCT
 
ORDER BY DIR
 
		
</cfquery>
</cffunction>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
what is the problem?
is the query giving error? returning wrong results?
if wrong results post sample data and the results returned by your query and he results you are expecting.
also try
(avg returns integer type if the expression is int so you might be loosing the fraction part)
--Avg Cycle
       AVG(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
             AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
             AND STEP_NAME = 'Order Info Provided To AE'
THEN
1.0 * DATEDIFF(DAY,PROJECT_ACTIVITY.PROJECT_RECD_DT,PMT_STEP_PROCESS_CYCLE.STEP_COMPLETED_DT)
                        ELSE 0
                        END) AS AVG
Avatar of Wirt

ASKER

Thanks sometimes i make it harder than i need to
hi, you could also try this so that it won't have errors in case STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#' never occurs.
<cffunction name="getPostTrainingData">
        <cfargument name="date_1">
        <cfargument name="date_2">
<cfquery name="getTrainingData" datasource="#dbname#" username="#dbuser#" password="#dbpw#">
SELECT  DIR,PRODUCT, 
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'                   AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
        THEN 1 ELSE 0 END) AS PRODS_REC,
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'
         AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
         THEN PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_QTY 
          ELSE 0 END) AS QTY_REC,
SUM(CASE WHEN PROJECT_RECD_DT >= '#ARGUMENTS.date_1#'
         AND PROJECT_RECD_DT <= '#ARGUMENTS.date_2#'
         THEN PMT_PRODUCT_PROCESS_CYCLE.MRC
         ELSE 0  END) AS REV_REC,
--Comp Acts
SUM(CASE WHEN PMT_STEP_PROCESS_CYCLE.STEP_NAME = 'Order Info Provided To AE'     
         AND STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'  
        THEN 1  ELSE 0 END) AS COMP_ACTS,
--Comp Qty      
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
         THEN PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_QTY
         ELSE 0 END)AS COMP_QTY,
--Comp Rev
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
        THEN PMT_PRODUCT_PROCESS_CYCLE.MRC 
         ELSE 0  END)AS COMP_REV,
--Avg Cycle
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
          AND STEP_NAME = 'Order Info Provided To AE'
THEN 
DATEDIFF(DAY,PROJECT_ACTIVITY.PROJECT_RECD_DT,PMT_STEP_PROCESS_CYCLE.STEP_COMPLETED_DT)
ELSE 0 END) /
CASE WHEN 
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
          AND STEP_NAME = 'Order Info Provided To AE'
THEN 
1
ELSE 0 END)=0
THEN 1
ELSE
SUM(CASE WHEN STEP_COMPLETED_DT >= '#ARGUMENTS.date_1#'
         AND STEP_COMPLETED_DT <= '#ARGUMENTS.date_2#'
          AND STEP_NAME = 'Order Info Provided To AE'
THEN 
1
ELSE 0 END) END AS AVG
 
FROM PMT_PRODUCT_PROCESS_CYCLE JOIN PMT_STEP_PROCESS_CYCLE
         ON PMT_PRODUCT_PROCESS_CYCLE.PROJECT_ID = PMT_STEP_PROCESS_CYCLE.PROJECT_ID
         JOIN PMT_PRODUCTS
         ON PMT_PRODUCT_PROCESS_CYCLE.PRODUCT_TYPE = PMT_PRODUCTS.PRODUCT 
         JOIN PROJECT_ACTIVITY
         ON PMT_PRODUCT_PROCESS_CYCLE.PROJECT_ID = PROJECT_ACTIVITY.PROJECT_ID
         JOIN PMT_USERS
         ON (PROJECT_ACTIVITY.PMT_USER_ID = PMT_USERS.PMT_USER_ID
                 OR PROJECT_ACTIVITY.CENT_OWNER = PMT_USERS.PMT_USER_ID)
WHERE DIR <> '0'
                AND ADSS <> '0'
                AND USER_ROLE = 'CSS'
                AND PRODUCT_SET = '#prodSet#'
                AND TRAINING_CLASS = '#className#'
 
GROUP BY DIR,PRODUCT
 
ORDER BY DIR
 
                
</cfquery>
</cffunction>

Open in new window