Posted by Ed Murphy on 11/10/07 07:39

Jayphi wrote:

> SELECT DISTINCT
> dbo._ProjectPositionViewStaff.OwnerPool,
> dbo._ProjectPositionViewStaff.ProjectName,
> CASE WHEN dbo._ProjectPositionViewStaff.Status
> = '1' THEN 'Active' WHEN dbo._ProjectPositionViewStaff.Status = '0'
> THEN 'Prospective' END AS EPM_Status,
> dbo._ProjectPositionViewStaff.Status,
> dbo.PROJECTTASK.sdr_StartDate AS [Start Date],
> dbo.PROJECTTASK.sdr_EndDate AS [End Date],
> dbo._ProjectPositionViewStaff.valueValue AS
> Value
> FROM dbo._ProjectView INNER JOIN
> dbo._ProjectPositionViewStaff ON
> dbo._ProjectView.ProjectName =
> dbo._ProjectPositionViewStaff.ProjectName INNER JOIN
> dbo.PROJECTTASK ON
> dbo._ProjectPositionViewStaff.ProjectOID = dbo.PROJECTTASK.projectOID
> WHERE (dbo._ProjectPositionViewStaff.Status = '1') OR
> (dbo._ProjectPositionViewStaff.Status = '0')

First, here are some abbreviations for readability, and a couple
other minor tweaks:

select distinct
ppvs.OwnerPool,
ppvs.ProjectName,
case ppvs.Status
when '1' then 'Active'
when '0' then 'Prospective'
end as EPM_Status,
ppvs.Status,
pt.sdr_StartDate as [Start Date],
pt.sdr_EndDate as [End Date],
ppvs.valueValue as Value
from dbo._ProjectView pv
join dbo._ProjectPositionViewStaff ppvs
on pv.ProjectName = ppvs.ProjectName
join dbo.ProjectTask pt
on ppvs.Project0ID = pt.project0ID
where ppvs.Status in ('0','1')

> What i would like to to is to add a parameter that would sort the
> projects by the project pool chosen. I'm at a loss can anyone point
> me in the right direction?

If you always want to sort by pool, then add this line:

order by ppvs.OwnerPool

There are a few different ways that this part could become more
complex. Rather than expand on them up front, I'll ask for some
more explanation of what you want to do in practice here.

[Back to original message]