Let me ask, how often is this data updated?
Your best bet might be to take a different approach from a simple
select. You may want to pre-aggregate the data somehow before it is
queried, for instance by creating a table to contain this information
and updating it once an hour. You could even take a hybrid approach by
storing aggregated data in another table and doing a UNION query to
retrieve on the most recent records.
Otherwise, the format of a PIVOT table should look something like this:
SELECT Group_ID, Supervisor_ID,
SUM(CASE WHEN DATEPART(month,file_date) = 1) THEN number_jobs /
number_employees END) AS 'January',
SUM(CASE WHEN DATEPART(month,file_date) = 2) THEN number_jobs /
number_employees END) AS 'February', ...
FROM sometable
M
-----Original Message-----
From: DURETTE, STEVEN J (AIT) [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 31, 2003 2:17 PM
To: CF-Talk
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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
