Pivot rows to Columns then case statement?

Question

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?
Thank you

Answer 1

Hi @J Like MIB
There are two reasons why you got nothing from the Output:
First, there are some simple mistakes in your JOIN conditions that may be typos. For example, and p1.PrjId1 = p2.PrjId2 should be and p1.PrjId2 = p2.PrjId2
By the way, the sample data you provided needs to be slightly modified. Check this:

create table projects (PrjId1 varchar(6), PrjId2 varchar(6), prjSchDate DATE);  
Insert into projects (PrjId1, PrjId2, prjSchDate) values  
('Prj022','000001', '2022-01-02'),  
('Prj022','000002', '2022-04-01')  
GO      

Second, since you JOIN prjProcess as p3 ,while you only select p2.prjProcessDesc, p2.prjProcessStatus .You can't see the columns in the p3 table if you don't select p3.prjProcessDesc, p3.prjProcessStatus

To get the results you want, please check this query:

;WITH CTE AS  
(			  
 SELECT P2.*,ROW_NUMBER()OVER(PARTITION BY P2.PrjId1,P2.PrjId2,prjProcessDesc ORDER BY prjProcessRecDate DESC)AS RNum  
 FROM #projects P1 JOIN #prjProcess P2 ON P1.PrjId1=P2.PrjId1 AND P1.PrjId2=P2.PrjId2  
)  
SELECT A.PrjId1,A.PrjId2,A.prjProcessDesc,A.prjProcessStatus,A.prjProcessRecDate  
      ,B.prjProcessStatus,B.prjProcessRecDate,DATEDIFF(DAY,A.prjProcessRecDate,B.prjProcessRecDate)AS NumberOfDays  
FROM (SELECT * FROM CTE WHERE prjProcessStatus='Accepted')A  
JOIN (SELECT * FROM CTE WHERE RNum=1)B ON A.PrjId1=B.PrjId1 AND A.PrjId2=B.PrjId2 AND A.prjProcessDesc=B.prjProcessDesc  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To calculate number of dates between each process stage you would use ISNULL(datediff(day, prjProcessRecDate, LEAD(prjProcessRecDate) over (partition by PrjId1, PrjId2)),0) as [Elapsed (Days)]

Answer 3

Not knowing what your actual end game need is... the below may help you... as you were after a double pivot, I've merged in to one which would need to be broken back out if required

select p.* from (
select p1.PrjId1, p1.PrjId2, concat_ws('-', p2.prjProcessDesc, p2.prjProcessStatus) as ProcessState, p2.prjProcessRecDate
from projects p1 
inner join prjProcess p2 on p1.PrjId1 = p2.PrjId1 and p1.PrjId2 = p2.PrjId2
and p2.prjProcessStatus in ('Approved', 'Finalized',  'Accepted')
) a 
pivot (max(prjProcessRecDate) for ProcessState in ([Project Submitted-Accepted],[Budgeting-Accepted],[Budgeting-Approved],[Engineering-Accepted],
  [Project Management-Accepted],[Development-Accepted],[Engineering-Finalized],[Development-Finalized],[Project Management-Finalized])) p 

Answer 4

Thank you NaomiNNN for pointing out the typos. Long day at work - eyes are crossed I guess.

LiHongMSFT-3908 pointed out a few more. What would I do without all of you? Thank you so much.

As always, thank you for viewing and replying to all.

Answer 5

Hello LiHong,
If I need to include a condition (where clause), perhaps for p1.prjSchDate or p2.prjProcessRecDate, where would I insert that?
I have added within CTE

ON P1.PrjId1=P2.PrjId1 AND P1.PrjId2=P2.PrjId2 P1.PrjId2=P2.PrjId2
where p1.prjSchDate BETWEEN '2022-01-01' AND '2022-04-01')

and I get 0 results and I have also added to exterior select and get 0 results.

AND A.PrjId2=B.PrjId2 AND A.prjProcessDesc=B.prjProcessDesc 
    where p1.prjSchDate BETWEEN '2022-01-01' AND '2022-04-01'

I have confirmed records do exists for the period.