Here is my dilemma
Each User could have multiple Records with different dates for each
assessment with multiple types of assessments.
What I need to do is calculate the average Score of the earliest date of
each assessment across users for a specific Org, and then the 2nd earliest
then 3rd...
It's to generate a bar chart showing the Org's overall success rate over
time
Lets call those Instances. Now i could easily add an Instance marker when
they input the record, check for the existence of an existing Assessment and
increment.
Buuuut, I'm told also that ill need to make a tool to allow them to fix
mustaches. IE: user 1 is really user 2...merge their data...
Ooo that instance idea just became a thorn in my side.
How might any of you go about this?
I'm Using SQL 2005 ColdFusion MX and my own server.
Here is my data
--Create table Statement
--***********************
CREATE TABLE #A(
[id] [int] IDENTITY(1,1) NOT NULL,
[user] [varchar](50) NOT NULL,
[org] [varchar](26) NOT NULL,
[date_time] [datetime] NOT NULL,
[assessment] [int] NOT NULL,
[Score] [numeric](5, 2) NULL
);
--Populate table Statements
--***********************
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','5/16/05 9:30 AM',1,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','4/11/07 3:12 PM',1,46.15);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','12/5/06 2:01 PM',1,82.05);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','6/13/07 10:35 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','9/21/06 2:02 PM',1,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','3/22/07 2:12 PM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','8/29/06 9:17 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','1/11/07 3:12 PM',1,56.41);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','5/27/05 10:58 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','5/16/05 9:30 AM',2,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','4/11/07 3:12 PM',2,46.15);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','12/5/06 2:01 PM',2,82.05);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','6/13/07 10:35 AM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','9/21/06 2:02 PM',2,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','3/22/07 2:12 PM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 1','8/29/06 9:17 AM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 1','1/11/07 3:12 PM',2,56.41);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 1','5/27/05 10:58 AM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','5/16/05 9:30 AM',1,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','4/11/07 3:12 PM',1,46.15);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','12/5/06 2:01 PM',1,82.05);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','6/13/07 10:35 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','9/21/06 2:02 PM',1,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','3/22/07 2:12 PM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','8/29/06 9:17 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','1/11/07 3:12 PM',1,56.41);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','5/27/05 10:58 AM',1,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','5/16/05 9:30 AM',2,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','4/11/07 3:12 PM',2,46.15);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','12/5/06 2:01 PM',2,82.05);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','6/13/07 10:35 AM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','9/21/06 2:02 PM',2,74.36);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','3/22/07 2:12 PM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('1','Test Group 2','8/29/06 9:17 AM',2,84.62);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('2','Test Group 2','1/11/07 3:12 PM',2,56.41);
INSERT INTO #A ([user],[org],[date_time],[assessment],[Score]) VALUES
('3','Test Group 2','5/27/05 10:58 AM',2,84.62);
I know it's more SQL than CFML but it's really for a CFML page :-)
--
--
Richard Dillman
[EMAIL PROTECTED]
(317) 916-8341
"Using ColdFusion is like having extra developers locked away in the
basement coding with you."
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Get the answers you are looking for on the ColdFusion Labs
Forum direct from active programmers and developers.
http://www.adobe.com/cfusion/webforums/forum/categories.cfm?forumid-72&catid=648
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:284661
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
