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

Answered not getting result in proper format

  • Saturday, February 16, 2013 8:25 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 9:46 AM
     
     
    Sign In to Vote
    0
    please post table structure with some sample data so that we can help you!!

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Saturday, February 16, 2013 10:15 AM
     
     Answered Has Code
    Sign In to Vote
    0

    you need to do some thing like max() on other columns 

    declare @s table (sno varchar(20),s1 varchar(20),s2 varchar(20),s3 varchar(20))

insert into @s
values('King',null,'check',null)
,('King','POWER',NULL,null)
,('King',null,NULL,'Rule')
,('King1',null,'check1',null)
,('King1','POWER1',NULL,null)
,('King1',null,NULL,'Rule1')

select * from @s

select sno,MAX(s1),MAX(S2),MAX(s3)from @s group by Sno

    and group by project name..look and see if this sample helps...

    SELECT     ProjectName,max(Plannedmilestone),max(Actualmilestone),max(futuremilestone)
from
(
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) Temp
group by  ProjectName

    Hope it Helps!!


    • Edited by Stan210 Saturday, February 16, 2013 2:57 PM
    • Marked As Answer by anuj astro Monday, February 18, 2013 6:27 AM
    •  
     
  • Monday, February 18, 2013 3:22 AM
     
     
    Sign In to Vote
    0

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (you did not). Code should be in Standard SQL as much as possible and not local dialect. Pictures are useless.

    >> 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 <<

    In any tiered architecture, the display formatting is done in the presentation layers, never in the database. This is fundamental software engineering, not just SQL!

    Want to try again and see if we can correct the design and get you a query that can be used by a report? And no more pictures, please

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Monday, February 18, 2013 6:28 AM
     
     
    Sign In to Vote
    0

    hi

    thanks stan,it worked