I'm not sure what's the best way to handle this - Sample: https://onecompiler.com/sqlite/3y3rs6m7p
The sample shows somewhat of the process a single project goes through. My task is to calculate the number of days each process takes (using prjProcessRecDate column) from Accepted to Approved / Finalized status for each division.
Projects table has other fields, but, I just need
In my mind, I'm wanting the following as the results.
prjProcessDesc prjProcessDesc prjProcessRecDate prjProcessDesc prjProcessRecDate NumberOfDays Engineering Accepted 2022-02-26 Finalized 2022-03-31 33 Budgeting Accepted 2022-01-18 Approved 2022-02-18 1 ProjectManagement Accepted 2022-02-26 Finalized 2022-05-05 xx Development ...
Anyway, I was hoping the following beginnings, while tedious, will at least convert the Engineering rows to columns
select p1.PrjId1, p1.PrjId2, p2.prjProcessDesc, p2.prjProcessStatus,p2.prjProcessRecDate from projects p1 inner join prjProcess p2 on p1.PrjId1 = p2.PrjId1 and p1.PrjId1 = p2.PrjId2 and p2.prjProcessDesc = 'Engineering' and p2.prjProcessStatus = 'Accepted' -- convert rows to columns inner join prjProcess p3 on p1.PrjId1 = p3.PrjId1 and p1.PrjId1 = p3.PrjId2 and p2.prjProcessDesc = 'Project Management'and p3.prjProcessStatus = 'Accepted' -- convert rows to columns
But it did not output anything. Why?
Also, feel free to suggest any cleaner / efficient alternatives. Pivot?