Steven, Try this if your DBMS is SQL Server:
select str((select sum(num_emp)/sum(num_jobs) from test3 where month(test_date)=1),5,1) as jan_rate, str((select sum(num_emp)/sum(num_jobs) from test3 where month(test_date)=2),5,1) as feb_rate... You get the idea. BTW, any jobs up your way? I'm looking. :) ~Dina ----- Original Message ----- From: "DURETTE, STEVEN J (AIT)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, July 31, 2003 1:16 PM Subject: OT: SQL data help. > Hi all, > > I have data that I am working with some data that I need to (for lack of a > better term) pivot. > > I have data that comes into a table in the format of: > Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date > Example: > Engineering, MGR1, 5, 6, 1-1-2003 > Engineering, MGR1, 5, 9, 2-1-2003 > Engineering, MGR2, 5, 6, 1-1-2003 > Engineering, MGR2, 5, 9, 2-1-2003 > Maintenance, MGR1, 5, 6, 1-1-2003 > Maintenance, MGR1, 5, 9, 2-1-2003 > Maintenance, MGR2, 5, 6, 1-1-2003 > Maintenance, MGR2, 5, 9, 2-1-2003 > > From this I figure the Rate (which is Number_Jobs / Number_Employees), so > the table is actually: > Group_ID, Supervisor_ID, Number_Employees, Number_Jobs, File_Date, RATE > Example: > Engineering, MGR1, 5, 6, 1-1-2003, 1.2 > Engineering, MGR1, 5, 9, 2-1-2003, 1.8 > Engineering, MGR2, 5, 6, 1-1-2003, 1.2 > Engineering, MGR2, 5, 9, 2-1-2003, 1.8 > Maintenance, MGR1, 5, 6, 1-1-2003, 1.2 > Maintenance, MGR1, 5, 9, 2-1-2003, 1.8 > Maintenance, MGR2, 5, 6, 1-1-2003, 1.2 > Maintenance, MGR2, 5, 9, 2-1-2003, 1.8 > > My problem is that I have to display it like: > > Group_ID, Supervisor_ID, Jan_Rate, Feb_Rate, ..., Dec_Rate > > Example: > Engineering, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, > 11.11, 12.12 > Engineering, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, > 11.11, 12.12 > Maintenance, MGR1, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, > 11.11, 12.12 > Maintenance, MGR2, 1.1, 2.2, 3.3, 4.4, 5.5, 6.6, 7.7, 8.8, 9.9, 10.10, > 11.11, 12.12 > > Right now, I have it working, but it seems very inefficient. > What I currently do is create a temp table in the format of the display. > Then I insert the Group, Supervisor, and Jan Data. After that I do an > update for each month. > > This is very slow and just looks wrong. Does anyone have any ideas how I > might be able to do a pivot (still lack of a better word)? > > Steve > > *****COST SAVINGS SUGGESTION***** > For pages that must be printed, change your print settings to print in > "grayscale" instead of color. > > > > Steve Durette > Mgr-Eng. & Const. Systems Support > 100 S. Main > Room 314 > Mt. Clemens, MI 48043 > Ofc: 586.466.7654 > Fax: 586.466.1109 > <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED] > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
