Olympic Project Cost Task Levels query

Olympic (now Rockton) project cost supports multiple levels of tasks stored in PC00300 that can be arranged in a hierarchy. 

If you're using that hierarchy, the query below will show you how to join PC00300 on itself and display all the levels.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

 

SELECT
       rtrim(pm.zCustomerID) as 'CUSTID',
       rtrim(pm.zCustomerName) as 'COMPNAME',
       rtrim(pm.zDateOpen) as 'CREATED',
       rtrim(pm.zProjectID) as 'PROJID',
       rtrim(pm.zProjectDescription) as 'PROJNAME',
       rtrim(Case when pm.zProjectStatusDDL = 1 then 'OPEN' else 'CLOSED' END) as 'PROJSTAT',
 
       rtrim(t1.zTaskID) as 'Level 1 TaskID',
       rtrim(Case when t1.zTaskStatusDDL = 1 then 'OPEN' else 'CLOSED' end) as 'WDSTAT',
 
       rtrim(t2.zTaskID) as 'Level 2 TaskID',
       rtrim(case when t2.zTaskStatusDDL = 1 then 'OPEN' else 'CLOSED' end) as 'Level 2 Status',
 
       rtrim(t3.ztaskID) as 'Level 3 TaskID',
       rtrim(t3.zTaskDescription) as 'Level 3 Task Desc',
       rtrim(case when t3.zTaskStatusDDL = 1 then 'OPEN' else 'CLOSED' end ) as 'Level 3 Status'
    FROM pc00200 pm     
       left join pc00300 t3 on pm.zProjectID = t3.zProjectID and t3.zTaskLevel = 3
       left join pc00300 t2 on t2.zProjectID = t3.zProjectid and t3.zHC_2 = t2.zHC_2 and t2.zHC_1 = t3.zHC_1 and t2.zTasklevel = 2
       left join pc00300 t1 on t1.zProjectID = t3.zProjectID and t3.zHC_1 = t1.zHC_1 and t1.zTaskLevel = 1

 

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences