I have a query (yes is very long,it works, trying to make it better ).
DECLARE @today DATETIME
SET @today = '2014-04-13'
;with CTE as(
Select d.csedept_name,d.csedept_id, Average = CASE WHEN d.csedept_question5 IS NULL
THEN (Select AVG(((cast(c.rating1 as Float)+ cast(c.rating2 as Float)+cast (c.rating3 as Float)+cast(c.rating4 as Float))/4))
From intranet.dbo.CSEReduxResponses c
Where c.employeedept = d.csedept_id
AND c.execoffice_status > 0
AND month(approveddate) = MONTH(@today))
ELSE (Select AVG(((cast(c.rating1 as Float)+ cast(c.rating2 as Float)+cast (c.rating3 as Float)+cast(c.rating4 as Float)+cast(c.rating5 as Float))/5))
From intranet.dbo.CSEReduxResponses c
Where c.employeedept = d.csedept_id
AND c.execoffice_status > 0
AND month(approveddate) = MONTH(@today))
END
from Intranet.dbo.CSEReduxDepts d
)
Select Top 2 Ranks = RANK() Over(Order By Average DESC) ,*
from CTE
well it get the top 2 of the data.
so im tyring to ouput those top two results:
<cfset highest_dept_name_average_runnerup = 0>
<cfset highest_dept_name_average_runnerup = 0>
<cfoutput query="getHighestDeptAverage">
<cfif Ranks eq 1>
<cfset highest_dept_name_average =csedept_name>
<cfset hihest_dept_average = average>
</cfif>
<cfif Ranks eq 2>
<cfset highest_dept_name_average_runnerup =csedept_name>
<cfset highest_dept_name_average_runnerup = average>
</cfif>
</cfoutput>
--------------------------------------------------------------
<cfoutput query="getHighestDeptAverage">
<h1><cfoutput>#self1#</cfoutput></h1>
<h3><cfoutput>Department-#csedept_name#</cfoutput></h3>
</cfoutput>
so right now it output like this, its repeateing 'cfif Rank eq 1'
how can i get it to only display once?
1
Reply
AdChoices