How to Output results based on year from query?

Question

I have a query:

<cfquery  name="pivotquery">
  SELECT employeedept,cse_name,YEAR,January,February,March,April,May,June,July,August,September,October,November,December
  FROM (
        SELECT month= datename(month,execoffice_date) 
              , YEAR =YEAR(execoffice_date)
              ,  employeedept
              , COUNT(*) as 'totalstars'
        FROM CSEReduxResponses
        WHERE execoffice_status = 1
        GROUP BY  employeedept
               , month(execoffice_date)
               , YEAR(execoffice_date)
               , DATENAME(month,execoffice_date)
      ) 
      AS r 

      JOIN csedept d ON d.cse_dept = r.employeedept

  PIVOT
    (
        SUM(totalStars)
        FOR [month] IN  (
                [January],[February],[March],[April],
                [May],[June],[July],[August],
                [September],[October],[November],[December]
        )
    ) 
    AS pvt

</cfquery>

This gets me all the data I want based on the month and year. I'm outputing the results in a table:

<table >
<thead>
<tr>
    <th>Department</th>
    <th>January</th>
    <th>Frebruary</th>
    .........
</tr>
</thead>
<tbody>
    <cfoutput query="pivotquery" >
    <tr>
        <td>#pivotquery.csedept_name#</td>
        <td>#pivotquery.January#</td>
        <td>#pivotquery.February#</td>
        ....... 
    </tr>
    </cfoutput>
</tbody>
</table>

Yes is outputting the data correctly. How can I get it to output the results in a separate table by year?

If you take a look at this sqlfiddle, it has 2014 and 2015 data. So I would like generate a separate table for each year. So with the data I created in the sqlfiddle, it would have 2 tables: one for 2014 and 2015.

I looked at the fiddle and saw nothing useful. What is supposed to go into the yearly tables, the monthly totals for the year, or the annual total? — Dan Bracuk
i think the sqlfiddle site is down or something , is that what you mean by saw nothing useful? — user3591637
Their site must be down or having problems. Currently it is either producing an error or blank page for me. — Leigh

Snipe656 Snipe656 · Accepted Answer · 2014-06-09T17:06:08

Depends on how you want things to be displayed. But since it looks like your data is ordered by year then you should be able to display by year via grouping your cfoutput by year. So something like:

<cfoutput query="pivotquery" group="YEAR">
<table >
<thead>
    <tr>
        <th>Department for #pivotquery.YEAR#</th>
    <th>January</th>
    <th>Frebruary</th>
    .........
    </tr>
</thead>
    <tbody>
        <cfoutput>
            <tr>
            <td>#pivotquery.csedept_name#</td>
            <td>#pivotquery.January#</td>
    <td>#pivotquery.February#</td>
    ....... 

            </tr>
        </cfoutput>
    </tbody>
</table>
</cfoutput>

How to Output results based on year from query?

1 Answers