Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
not getting result in proper format

Answered not getting result in proper format

  • Saturday, February 16, 2013 8:22 AM
     
     
    Sign In to Vote
    0

    hello everybody,

    i have created a query in sql,it is displaying correct results,but it is not showing result according to the format which i need

    as you can see for project name test_final 2,phase5 completed is in actual milestone as well as in planned milestone,but result is coming in different rows,i want the result to appear in same row,if anybody has any idea,plz let me know,any help will be greatly appreciated

    i am also mentioning the query below which i have created

    SELECT     MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskName AS Plannedmilestone, NUll AS Actualmilestone, NUll AS futuremilestone
    FROM         MSP_EpmTask_UserView INNER JOIN
                          MSP_EpmProject_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
    WHERE     (MONTH(MSP_EpmTask_UserView.TaskBaseline0StartDate) = '2') AND (YEAR(MSP_EpmTask_UserView.TaskBaseline0StartDate) = 2013) AND 
                          (MSP_EpmTask_UserView.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView.Area = 'Mumbai')
    UNION all
    SELECT     MSP_EpmProject_UserView_2.ProjectName, NUll AS Plannedmilestone, MSP_EpmTask_UserView_2.TaskName AS Actualmilestone, NUll AS futuremilestone
    FROM         MSP_EpmTask_UserView AS MSP_EpmTask_UserView_2 INNER JOIN
                          MSP_EpmProject_UserView AS MSP_EpmProject_UserView_2 ON MSP_EpmProject_UserView_2.ProjectUID = MSP_EpmTask_UserView_2.ProjectUID
    WHERE     (MONTH(MSP_EpmTask_UserView_2.TaskActualFinishDate) = '2') AND (YEAR(MSP_EpmTask_UserView_2.TaskActualFinishDate) = 2013) AND 
                          (MSP_EpmTask_UserView_2.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView_2.Area = 'Mumbai')
    UNION all
    SELECT     MSP_EpmProject_UserView_1.ProjectName, NUll AS Plannedmilestone, NUll AS Actualmilestone, MSP_EpmTask_UserView_1.TaskName AS FutureMilestone
    FROM         MSP_EpmTask_UserView AS MSP_EpmTask_UserView_1 INNER JOIN
                          MSP_EpmProject_UserView AS MSP_EpmProject_UserView_1 ON MSP_EpmProject_UserView_1.ProjectUID = MSP_EpmTask_UserView_1.ProjectUID
    WHERE     (MONTH(MSP_EpmTask_UserView_1.TaskStartDate) = '2' + 1) AND (YEAR(MSP_EpmTask_UserView_1.TaskStartDate) = 2013) AND 
                          (MSP_EpmTask_UserView_1.TaskIsMilestone = '1') AND (MSP_EpmProject_UserView_1.Area = 'Mumbai') order by ProjectName

     

All Replies

  • Saturday, February 16, 2013 2:50 PM
     
     Answered
    Sign In to Vote
    0

    Well, You can create a stored procedure to get this query.

    Then take the result into TempTable

    Rephrase the result inorder to get the result as below.

    test_final1 phase 3   completed NULL NULL
    test_final2 phase 5 completed phase 5 completed d

    This could be acheived easily. Either with a while loop or a cursor.

    Then have as the dataset to the report inorder to get the result straight.

    This is one way of solving this issue.

    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.