question

JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 asked NaomiNNN commented

Pivot rows to Columns then case statement?

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In answer to why it didn't output anything


  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**

p1.PrjId*2* = p2.PrjId2?


1 Vote 1 ·

you also have
p*2*.prjProcessDesc = 'Project Management'


that should be p3

0 Votes 0 ·
LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered JLikeMIB-6608 commented

Hi @JLikeMIB-6608
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:
201190-image.png

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.


image.png (11.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Perhaps it's that site ... it says error on DAY: no such column.
I'm thinking it's DATEDIFF - I have changed to DD and it still errors out. I'll check on SSMS.
Thank you for viewing and your suggestions.

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered JLikeMIB-6608 commented

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)]

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for viewing and suggestion.
I'll incorporate that in the code.

0 Votes 0 ·
RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered JLikeMIB-6608 commented

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 


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for viewing and replying.
Error: near "pivot": syntax error.

0 Votes 0 ·

Ran in SSMS and there were no errors. Weird ...
This is REALLY interesting I'll have to look further into it.

0 Votes 0 ·

That site you posted to is using SQLLite... I suspect pivot is a bit more advanced for "Lite"

1 Vote 1 ·

That's probably why.
I tried a few things (PIVOT, DATEDIFF) that didn't work on the site and it works on SSMS.

0 Votes 0 ·
JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered JLikeMIB-6608 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered LiHongMSFT-3908 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JLikeMIB-6608
If you want to filter data with dates between '2022-01-01' AND '2022-04-01', then you need to add the 'where' clause in the CTE.

 ;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
  WHERE p1.prjSchDate BETWEEN '2022-01-01' AND '2022-04-01'
 )
 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

I test this on my local side, and the data can be queried normally.

0 Votes 0 ·
JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered NaomiNNN commented

LiHong,
Thank you.
One last question (hopefully), I have a project that had a couple of lines with same prjProcessDesc - by Project Management and prjProcessStatus - corrections required.

For example
('Prj022','000001','Project Management','Corrections Required','2022-03-08','Project Manager','2022-04-01'),
('Prj022','000001','Project Management','Corrections Required','2022-04-02','Project Manager','2022-05-08'),

So in the example, I would like the prjProcessRecDate = '2022-04-01' (bolded in example above) to Finalized date (longest NumberOfDays). I tried including prjProcessStatus in ROW_NUM Partition by but that didn't change anything.

Thank you very much


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Can you please start a new thread with clear sample and requirements? Your project status is 'Corrections Required' but I thought you wanted difference between Accepted and Approved / Finalized ?

0 Votes 0 ·

You know what - you are 100% right. Come to think of it - I don't need the dates for the processes in the middle, just the beginning (Accepted / Assigned) and end ( Approved / Finalized) for each division. I'll have to check the data again.

Now you make me wonder if that project doesn't have a prjProcessStatus of (Accepted).
Sorry for the confusion - this dataset is so messy, I'm trying to solve the odd situations. I'll check the datasets again and see if that's the case.

EDIT: And yes, looks like there a few odd projects that did not have either Accepted / Assigned for each of division. The 1st record for some of the divisions before Approved / Finalized / Ready can be Pending, Corrections Required besides the Assigned / Accepted. I don't have access to the applications - so, I don't know what situations / when the users will pick. I can just see the datasets. Sorry for the confusions.

0 Votes 0 ·

No problem and the statuses do look correct to me. This is similar to what we have in our Version 1 software which we use for the sprint tasks.

0 Votes 0 ·