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
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
I do not know if I understand correctly, but try something this:
(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)