We are using MS SQL 2000.
I do some of the math in the db.
I am very weak with rollup, so maybe you can see what I am missing.
Example:
Project:
Task1
--Task1.1
--Task1.2 -- Has hours
--Task1.2.1
--Task1.2.2 -- Has hours
--Task1.2.3
--Task1.3
Task2
Task3---Has hours
The rollup can get me the totals for a Project, CostArea and each individual
task, but not the roll ups for tasks like Task1.2 or Task1.
Here's a query I am playing with:(limiting to one project for faster testing
:o).
SELECT P.PROJ_NAME, TF.TEXT_VALUE AS CostArea, T.TASK_UID, T.TASK_NAME,
GA.res_name, SUM(GA.Cost) AS cost, SUM(GA.HOURS) AS hours
FROM TBLAllProjects P INNER JOIN
ProjectServer_2003.dbo.MSP_TASKS T ON P.PROJ_ID =
T.PROJ_ID LEFT OUTER JOIN
TBLGovActuals GA ON P.PROJ_ID = GA.proj_id AND
T.TASK_UID = GA.TASK_UID LEFT OUTER JOIN
ProjectServer_2003.dbo.MSP_TEXT_FIELDS TF ON
T.TASK_UID = TF.TEXT_REF_UID
WHERE (TF.TEXT_FIELD_ID = 188744479) AND (TF.TEXT_VALUE IS NOT NULL) AND
(T.TASK_UID > 0) AND (P.PROJ_ID = 840)
GROUP BY P.PROJ_NAME, TF.TEXT_VALUE, T.TASK_UID, T.TASK_NAME, GA.res_name,
T.TASK_NAME
WITH ROLLUP
The only fields that tell you were a Task is, are: Task_lvl(1,2,3, etc)
and Task_number(1, 1.1, 1.2, 1.1.1, ext).
The Query doesn't know that 1.1, 1.1.1 and 1.2 get rolled up into 1.
So I don't see were it's possible to do the roll ups for the top lvl tasks.
Thanks
Rodney
-----Original Message-----
From: James Holmes [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 5:22 AM
To: CF-Talk
Subject: Re: StructFindKey Path and evaluate
Yes, I was thinking that a cube of this would probably provide most of the
desired info in a query (although I haven't looked closely enough to be
sure). Does your DB do cube and rollup?
On 4/20/06, Thomas Chiverton <[EMAIL PROTECTED]> wrote:
> On Wednesday 19 April 2006 17:24, Bruce, Rodney S C-E LCMC
> HQISEC/Signal Solutions wrote:
> > If there is a better way to do this, please let me know. I don't
really
> > like the way I am doing it, but unfortunetly I havent come up with
> > anything better and I do seem to do things the hard way.
>
> Getting the DB to do the maths ?
--
CFAJAX docs and other useful articles:
http://jr-holmes.coldfusionjournal.com/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238334
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
