COLDFUSION: How to GROUP by the first column and convert second column into three separate columns

Question

This is a sample of the RAW DATA that I working with from the "employeeRatings" Table before the cfquery output:

        (showcasing employeeID:1128 for the month of May)

employeeID  |   Possible_Factor  |   Factor   |  ratingDate
=======================================================================
 1128       |        .1          |  .1        | 5/25/2013 2:05:13 PM 
 1128       |        .1          |  .0        | 5/22/2013 9:30:43 AM 
 1128       |        .2          |  .1        | 5/17/2013 9:42:09 AM 
 1128       |        .1          |  .1        | 5/13/2013 8:07:15 AM 
 1128       |        .1          |  .0        | 5/10/2013 7:52:51 AM 
 1128       |        .4          |  .0        | 5/6/2013 12:41:12 PM

Here's the cfquery (SQL statement):

SELECT ROUND(100 * (SUM(Factor) / SUM(Possible_Factor)), 2) AS employeeRating, CONVERT(CHAR(4), ratingDate, 100) + CONVERT(CHAR(4), ratingDate, 120) AS month, employeeID, DATEADD(MONTH, DATEDIFF(MONTH, 0, ratingDate), 0) AS shortdate 
FROM employeeRatings
GROUP BY CONVERT(CHAR(4), ratingDate, 100) + CONVERT(CHAR(4), ratingDate, 120), DATEADD(MONTH, DATEDIFF(MONTH, 0, ratingDate), 0), employeeID 
ORDER BY employeeID, DATEADD(MONTH, DATEDIFF(MONTH, 0, ratingDate), 0) DESC

After the cfquery, the output will look like this:

employeeID  |   employeeRating   |   month      |  shortdate
=======================================================================
 1128       |        30          |  May 2013    | 5/1/2013 12:00:00 AM 
 1128       |        60          |  April 2013  | 4/1/2013 12:00:00 AM
 1128       |        90          |  Jan 2013    | 1/1/2013 12:00:00 AM
 7310000    |        95          |  April 2013  | 4/1/2013 12:00:00 AM
 7310000    |        85          |  Mar 2013    | 3/1/2013 12:00:00 AM
 7310000    |        75          |  Feb 2013    | 2/1/2013 12:00:00 AM
 7310000    |        55          |  Jan 2013    | 1/1/2013 12:00:00 AM
 444981     |        27          |  Mar 2013    | 3/1/2013 12:00:00 AM
 444981     |        77          |  Jan 2013    | 1/1/2013 12:00:00 AM
 444981     |        97          |  Nov 2012    | 11/1/2012 12:00:00 AM
 444981     |        37          |  Sept 2012   | 9/1/2012 12:00:00 AM
 444981     |        47          |  Aug 2012    | 8/1/2012 12:00:00 AM

I need to take an employee and list their LAST THREE ratings (if month is null, skip the null month and get the next month with a rating in order to showcase last three documented ratings). This is a dynamic cfquery that list over 200 employees. The following is the desired output:

supplierID  |   LastRating   |   SecondLastRating  |   ThirdLastRating
======================================================================
 1128       |        30      |       60            |         90
 7310000    |        95      |       85            |         75
 444981     |        27      |       77            |         97

I am using ColdFusion on a SQL Server 2000 (compatibility 80), however the ColdFusion version I am using do not support cfloop group attribute. I would like to take the new output and put it into a new query, so it can be JOINED with another query. A solution = starbucks gift from FB ;) Thank you everyone for your time and consideration!!!!

Instead of grouped cfloop use grouped cfoutput, valid in all versions of CF — duncan, Jun 05 '13 at 15:26
Is modifying your original db query an option? If so, please update the question tags to indicate your database type/version. — Leigh, Jun 05 '13 at 16:43
Hi all! First, thank you all for taking the time to review my question. — Enchauva, Jun 05 '13 at 17:58
@duncan, that works! I'm currently using James Mohler's solution that utilizes that attribute. :) Thank you! — Enchauva, Jun 05 '13 at 18:00
@Leigh, modifying the original db query is not an option. I am currently this data from a db that I have no control over. :( — Enchauva, Jun 05 '13 at 18:01
@Enchauva - No, I meant do you have control over the `cfquery` code (not the database table). If you can alter the cfquery, it is possible to do this in SQL. If not, you will have to use CF code as others suggested. — Leigh, Jun 05 '13 at 18:06
@Leigh - Oh, my mistake. Yes I do have control over the cfquery. I would love to see what road you are leading to with this :) — Enchauva, Jun 05 '13 at 18:19
@Enchauva - Okay. I cannot do this until later, but I will see if I can mock up an example. — Leigh, Jun 05 '13 at 18:30
@Leigh - Awesome!!! No problem... I am extremely appreciative for all the different methods and approaches. I look forward for your example!!!! :D — Enchauva, Jun 05 '13 at 18:37
What is the original cfquery that is pulling up that list? And what does that table look like that you're pulling from? I agree with Leigh. This should be sorted properly in the query and not through code. Depending on how that original query is structured, you might get unexpected return data. If you can do PIVOT in your database, that would be the way to go. If not, you'll have to pivot manually. What version of SQL Server are you using? — Shawn, Jun 06 '13 at 15:11
As for the SQL Server version: I believe it's SQL Server 2000 being that the compatibility level is 80. — Enchauva, Jun 06 '13 at 17:26
@Leigh - Totally agreed! Thank you for the suggestion... done :) — Enchauva, Jun 06 '13 at 18:08

score 3 · Answer 1 · answered Jun 05 '13 at 15:23

3

Here is a ColdFusion only solution

<table>
<tr>
    <td>SupplierID</td>
    <td>LastRating</td>
    <td>SecondLastRating</td>
    <td>ThirdLastRating</td>
</tr>
<cfoutput name="qrySupplier" group="employeeID">
<cfset Rating  = 0>
<tr>
    <td>#employeeid#</td>
    <cfoutput>
       <cfset Rating++>
       <cfif Rating LTE 3>
           <td>#employeerating#</td>
       </cfif>
     </cfoutput>
</tr>
</cfoutput>
</table>

answered Jun 05 '13 at 15:23

James A Mohler

11,060
15
46
72

This works!!!!!! I'm working using it!!!! Thank you so MUCH!!! This was the tough part for me, but now I'll see I can create a new query off of this. Regardless... I'll try to find you on FB and starbuck-gift you ^_^ Thank you again! – Enchauva Jun 05 '13 at 18:05
Now the trick that I'm trying to figure out is how to take this and create a new query. And suggestions? – Enchauva Jun 06 '13 at 17:27
A Pivot table would be useful – James A Mohler Jun 06 '13 at 17:30
@Enchauva - In this specific case, simulating a pivot in 2000, would get a little ugly/unmanageable - unless you have the ability to create views or temp tables. Do you? While not ideal, you could still use CF code. See my updated answer. – Leigh Jun 11 '13 at 16:17

score 3 · Answer 2 · edited May 23 '17 at 11:43

SQL Server 2005+

Another option is using SQL Server's PIVOT operator

First use ROW_NUMBER() to rank the records by employee and date. (Note: If your table does not contain an actual datetime column, you could substitute an identity column, or cast the "month" into a datetime using convert()).

    SELECT  employeeID
            , employeeRating
            , ROW_NUMBER() OVER ( 
                    PARTITION BY employeeID 
                    ORDER BY employeeID, theRatingDateCol DESC
            ) AS Row
    FROM   yourTable
    ...

Results:

employeeID  employeeRating Row
----------- -------------- --------------------
1128        30             1
1128        60             2
1128        90             3
444981      27             1
444981      77             2
444981      97             3
444981      37             4
7310000     95             1
7310000     85             2
7310000     75             3
7310000     55             4

Then PIVOT the results of the top three (3) rows:

    ... 
    PIVOT
    (       
            MIN(employeeRating)
            FOR Row IN ( [1],[2],[3]) 
    )

Full Query:

SELECT pvt.employeeID
        , pvt.[1] AS LastRating  
        , pvt.[2] AS SecondLastRating  
        , pvt.[3] AS ThirdLastRating  
FROM (
        --- order by employee and rating date (descending)
        SELECT  employeeID
                , employeeRating
                , ROW_NUMBER() OVER ( 
                    PARTITION BY employeeID 
                    ORDER BY employeeID, theRatingDateCol DESC
                ) AS Row
        FROM   yourTable
    ) data
    PIVOT
    (   -- take top 3 values
        MIN(employeeRating)
        FOR Row IN ( [1],[2],[3]) 
    ) pvt

Results:

employeeID  LastRating  SecondLastRating ThirdLastRating
----------- ----------- ---------------- ---------------
1128        30          60               90
444981      27          77               97
7310000     95          85               75

SQL Server 2000

Unfortunately SQL Server 2000 and earlier do not support either of those functions. While not as slick as PIVOT, you can still simulate it using a subquery and CASE.

First, use a subquery in place of ROW_NUMBER(). Essentially you count the number of records with an earlier rating date, and use it in place of a row number. Note: This assumes the rating dates are unique per employee. If they are not you will need to add another column to break the tie.

Then use CASE to examine the row numbers and generate columns for the first three records:

SELECT  r.employeeID
        , MAX( CASE WHEN r.Row = 0 THEN r.EmployeeRating ELSE 0 END ) AS LastRating  
        , MAX( CASE WHEN r.Row = 1 THEN r.EmployeeRating ELSE 0 END ) AS SecondLastRating  
        , MAX( CASE WHEN r.Row = 2 THEN r.EmployeeRating ELSE 0 END ) AS ThirdLastRating  
FROM  (
        SELECT  m.employeeID
                , m.employeeRating
                , m.theRatingDate
                , (  SELECT COUNT(*)
                     FROM   yourTable cnt
                    WHERE  cnt.employeeID = m.employeeID
                    AND    cnt.theRatingDate > m.theRatingDate
                ) AS Row
        FROM   yourTable m
        GROUP BY m.employeeID
            , m.employeeRating
            , m.theRatingDate
        ) r
WHERE  r.Row <= 2
GROUP BY r.employeeID

ColdFusion

A last option is to use ColdFusion. You could adapt James Mohler's answer to populate a separate "pivot" query. Before the query loop, create a new query object, and name the rating columns sequentially ie rating1,rating2,rating3. Inside the outer loop, add a row for each employee. Finally, use the counter to populate the first three columns inside the inner loop.

Note: The original query must be ordered by employeeID, shortDate DESC or the code will not work correctly.

<cfset newQuery = queryNew("employeeID,rating1,rating2,rating3", "integer,Decimal,Decimal,Decimal")>

<cfoutput query="originalQuery" group="employeeID">
    <!--- add new employee row --->
    <cfset ratingRow = queryAddRow(newQuery, 1)>
    <cfset newQuery["employeeID"][ratingRow] = employeeID>

    <!--- initialize rating counter --->
    <cfset ratingIndex = 0>
    <cfoutput>
        <cfset ratingColumn++>
        <!--- populate top 3 rating columns --->
        <cfif ratingColumn lte 3>
            <cfset newQuery["rating"& ratingColumn][ratingRow] = employeeRating>
        </cfif>
    </cfoutput>
</cfoutput>

Thanks Leigh!!! I tried your coding, however my server is SQL Server (80), which doesn't support the "ROW_NUMBER()" function. Only SQL Server 2005 (90), SQL Server 2008 (100), or SQL Server 2012 (110) supports it. Sad day because your solution looks great and promising!!!! :( — Enchauva, Jun 06 '13 at 17:16
Oh, then `PIVOT` is out too. Both were introduced after 2000. However, you can simulate it with a subquery and `case`. It is not as pretty, but I have to do that for legacy systems. I will post an updated example for SQL Server 2000. — Leigh, Jun 06 '13 at 17:34
Leigh... you're just epic!!! Right now reviewing and plugging your solution. I'll provide a status update within the hour! *crossing fingers* — Enchauva, Jun 06 '13 at 18:11
Once again, thank you Leigh! Everything work, however the "CASE WHEN r.Row = 0..." statement isn't capturing the right rating, and I understand why. When we ask the inner-inner statement to do a row count, it is counting the rows before calculating the employee rating summary. The original "employee work assignment" table has varies amount of work for a particular employee for each month which the cfquery crushes that data into a monthly rating for an employee for a particular month. I will provide an example of the raw data table before the query... this is EXTREMELY close to the solution!!! — Enchauva, Jun 06 '13 at 20:18
(Edit) Well technically you could make it work, but given the new requirements *and* existing restrictions, the sql required would get a little ridiculous ... What you really need is to create a [`view`](http://msdn.microsoft.com/en-us/library/aa214068%28v=SQL.80%29.aspx), or temp table, and use it in place of `yourTable`. Is that permission available in your environment? If not, you might be better off using CF at this point. Without knowing why you need a pivot to join with another query it is hard to say if that is the *only* option though. — Leigh, Jun 07 '13 at 08:29

score 1 · Answer 3 · answered Jun 05 '13 at 15:20

Here is something that you can try to get you started. I left comments. I didnt have access to build a quick table, so its untested but could be a good starter. I took into account the fact that your employee/supplier may have more or less than 3 ratings.

<!--- Counter to count ratings  --->
<Cfset x=0>
<table width="600" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>supplierid</td>
    <td>last rating</td>
    <td>second last rating</td>
    <td>thirdlastrating</td>
  </tr>
<!--- Group by employee --->
<cfoutput query="yourQuery" group="employeeid">
    <!--- if previous employee had less then 3 ratings, close off table --->
    <cfif x gt 0 and x lt 3>
         <cfif x eq 1><td>&nbsp</td><td>&nbsp;</td></tr></cfif>
         <cfif x eq 2><td>&nbsp;</td></tr></cfif>
    </cfif>
    <!--- Loop through employee --->
  <tr>
    <td>#employeeid#</td>
    <!--- Check counter to make sure we are only doing 3 ratings per line --->
    <cfif x lt 3>
        <cfoutput>
        <td>#employeerating#</td>
        <cfset x=x+1>
        </cfoutput>
    </cfif>
    <!--- If at the 3rd rating, close off the row --->
    <cfif x eq 3>
        </tr>
        <cfset x=0>
        <!--- if at 3rd rating, reset counter --->
    </cfif>
</cfoutput>
</table>

it worked for the first employee, but didn't populate the rating for the rest of the employees. I'll try it again to triple check that I didn't over look something. Regardless, I do appreciate your support and your approach!!!! :D — Enchauva, Jun 05 '13 at 18:07

COLDFUSION: How to GROUP by the first column and convert second column into three separate columns

3 Answers3

SQL Server 2005+

SQL Server 2000

ColdFusion