1 Reply Latest reply on Apr 16, 2014 3:24 AM by BKBK

    output the right information

    no_name_123

      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'

       

      picture.PNG

      how can i get it to only display once?