Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2012
    Posts
    5

    Unanswered: SQL Server2008 Query

    Project table (inv_investments)


    Code Id Name Description

    12706 50278531 networoperations status is green


    Phase table (umt_phases)

    phaseid phasecode phasename

    1 G1 Planning

    2 G2 Requirements

    3 G3 Design

    4 Build Development

    5 Test Test

    6 Implement Deploy


    Prtask table


    prprojectid prexternalid

    5032401 G1

    5032401 G2

    5032401 G3


    5032401 Test



    i have made the left outer join of phases table with the prtask table to get the deploy and

    implement phases as well for the projects.

    I am getting the project phases and details cooresponding to the project id having all phases or atleast one of the phases defined in the prtasktable (prextrernalid).


    Scenario is that if prtask table does not have entries for the prexternalids (for certain projects not having the tasks) ---the projects and the details (project id and projectname) are not listed
    since no values are returned from the table.


    Project phases should be shown as blank in the crystal report for the projects not having tasks

    The query attached gives the projectid,project phases and details cooresponding to the project id having all phases or atleast one of the phases defined in the prtasktable (prextrernalid).


    ex:The project given below does not have the required tasks defined in the prtask table


    Project id :50275837

    Project Name : network operations




    Phases Status Startdate End date


    Planning

    Requirements

    Design


    Development

    Test

    Deploy


    Can you please suggest if any hardcoding at crystal report or query can be modified ?

    I have to show all phases in the crystal report even i having no values for the project id selected


    Any help is greatly appreciated.

    Thank you!

    Regards,
    Aru
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    I do not know if I understand correctly, but try something this:

    Code:
    select 
        pj.Code, 
        pj.Id, 
        pj.Name, 
        pj.Description, 
        ph.phasename, 
        (case when pt.prexternalid is null then 'No' else 'Ok' end) as Stat
    from Project as pj
    cross join Phase as ph
    left join Prtask as pt on (pt.prprojectid = pj.Id) and 
                              (pt.prexternalid = ph.phasecode)
    Hope this helps.

  3. #3
    Join Date
    Jul 2012
    Posts
    5
    Hi Imex,

    Thanks for the update and quick response. I will check and let you know.

    Thanks and Regards,
    Aru

  4. #4
    Join Date
    Jul 2012
    Posts
    5
    Thanks Imex.It worked.Appreciate your help.

  5. #5
    Join Date
    Jul 2012
    Posts
    5

    Transpose of Row to columns in sql server

    Hi all,

    I have got the query results in rowwise for phases for a given project id.

    I am in need of phases in column wise and details corresponding to the phases

    Database used; SQL server2008 and Reporting tool : Crystal reports

    ex:

    Phases Planning Requirements Design Development Testing Implement

    I would like to have the results as per the attched excel


    Can you please help me at the earliest?

    Thanks and Regards,
    Arumugam B
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •