November 17, 2011 at 2:52 am
Hello,
I am trying to create reports with TimeByday (MSP 2007 server, MS Visual Studio 2005, SQL Server 2005). Some of our tasks with duration being zero are not pulled, but they do exists.
Here are examples of VERY SIMPLE queries showing the root cause. In the 1st 2 queries, there is no result but in the 3rd yes...
The 3rd used, made with dbo.MSP_EpmAssignmentByDay_OlapView, is too slow when we pull the report. Moreover, it gives me the baseline TimeByDay and not the current one...
Thanks for your help
MJC
Query 1 :
SELECT TaskUID, TimeByDay, ProjectUID
FROM dbo.MSP_EpmTaskByDay
WHERE (TaskUID = '8da905da-ff08-4510-ab5e-42b440b3d4e3')
Results: Nothing
Query 2:
SELECT TaskUID, TimeByDay, ProjectUID
FROM dbo.MSP_EpmTaskByDay_UserView
GROUP BY TimeByDay, ProjectUID, TaskUID
HAVING (TaskUID = '8da905da-ff08-4510-ab5e-42b440b3d4e3')
Results: Nothing
Query 3:
SELECT TaskUID, TimeByDay, ProjectUID
FROM dbo.MSP_EpmAssignmentByDay_OlapView
GROUP BY TaskUID, TimeByDay, ProjectUID
HAVING (TaskUID = '8da905da-ff08-4510-ab5e-42b440b3d4e3')
Results:
Col 1: TaskUID = '8da905da-ff08-4510-ab5e-42b440b3d4e3'
Col 2: TimeByDay= 06.04.2012 00:00:00
Col 3: ProjectUID= dfb70a1b-8964-4a66-ac0e-cc6ed13e0443
November 17, 2011 at 2:58 am
Is it possible to post the DDL's of those 3 different tables, some sample input and the desired result? (read the link in my signature about posting questions).
It is not really clear for me what you are trying to do, so some more elaboration would be helpful.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 17, 2011 at 3:43 am
Hello,
Thank you for trying to help me. 1st time posting anything and I am a beginner in it.
The actual Data of this TASKUID are:
Duration= zero
TaskStartDate= 10 Apr. 2012 00:00:00
TaskFinishDate= 10 Apr. 2012 00:00:00
Baseline0 TaskStartDate: 06 Apr. 2012 00:00:00
Baseline0 TaskFinishDate: 06 Apr. 2012 00:00:00
The expected result of any of these queries should be
TimeByDay=10 Apr. 2012 00:00:00
If successful, I can then create a matrix report with Visual Studio.
As written, the 3rd query does provide some results but not the expected one. Indeed, it gives the Baseline0 TaskStartDate (04 Apr 2012) instead of the current TaskStartDate (10 Apr. 2012)
I have attached the DDL. If something is still missing, let me know.
Thanks again.
Marc
November 17, 2011 at 10:42 am
Just a quick observation:
The "GROUP BY" and "HAVING" clauses should only be used when you have aggregate functions in your query. Not too sure why you have both in query 2 and 3, but it isn't necessary.
Martin.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply