Below are sample queries that can be used when creating a creating a custom SQL import from Deltek Ajera:
Project level:
SELECT prjID AS ProjectNumber,
prjDescription AS ProjectName FROM AxProject
WHERE prjID <> ''
AND prjParent IS NULL
AND prjStatus = '1'
Phase level:
SELECT b.prjID AS ParentProject, a.prjID AS
PhaseNumber, a.prjDescription AS PhaseName From AxProject a
INNER JOIN
(SELECT prjKey, prjID FROM AxProject
WHERE prjID <> ''
AND prjParent IS NULL
AND prjStatus = 1) b
ON a.prjParent = b.prjKey
WHERE a.prjID <> '' AND a.prjPhaseLevel1 <> ''
AND a.prjStatus = 1
AND a.prjDescription <> 'No Phase'
Task level:
SELECT a.prjPhaseLevel2 AS TaskNumber, a.prjDescription AS TaskName, c.prjDescription AS ProjectNumber, b.prjPhaseLevel1 AS PhaseNumber FROM AxProject a
INNER JOIN
(SELECT prjPhaseLevel1, prjKey, prjParent FROM AxProject
WHERE prjPhaseLevel1 <> ''
AND prjStatus = 1
AND PrjDescription <> 'No Phase') b
ON a.prjParent = b.prjKey
INNER JOIN
(SELECT prjDescription, prjkey FROM AxProject
WHERE prjParent IS NULL
AND prjStatus = '1') c
ON b.prjParent = c.prjKey
WHERE a.prjPhaseLevel2 <> ''
AND a.prjStatus = 1
0 Comments