TextJoin the rows based on the Id's dates and sequence

I have been trying to concatinating the Specific column based on the ID's, dates and the sequence which are avaialble in the separate columns.

I have used 3 steps to get the result and the result is accurate but i want to use a single formula or Google sheets Query to achieve the final result.

I have added a google sheets for your reference and i would really appreciate your help in this regards.

1st step In this step i just Sorted out the data

=QUERY({'Raw Data'!A:F}, "SELECT Col2, Col3, Col4, Col5 Where Col2 is not null ORDER BY Col2 ASC, Col4 ASC, Col3 ASC")

2nd step From Sorted out data i have taken 2 column and make them unique then used textjoin function to concatenate the notes with before and after brackets.

=UNIQUE(QUERY(A2:D, "SELECT A,C"))
=TEXT(G3,"MM-DD-YYYY")&": ("&TEXTJOIN(" ",TRUE,FILTER(D:D,A:A=F3,C:C=G3))&")"

3rd step after all above steps i pulled the only unique id's then based on unique id's textjoin the notes

=UNIQUE(F2:F)
=TEXTJOIN(" ",TRUE,FILTER(H:H,F:F=L3))

Solution

Added formula to your sheet to test:

=index(let(a,sort({B2:E},1,1,3,1,2,1),b,unique(filter({index(a,,1),index(a,,3)},index(a,,1)<>"")),c,map(index(b,,1),index(b,,2),lambda(d,e,"("&join(" ",filter(index(a,,4),index(a,,1)=d,index(a,,3)=e))&")")),byrow(unique(tocol(index(b,,1),1)),lambda(z,{z,join(" ",unique(filter(TEXT(index(b,,2),"mm-dd-yyyy")&": "&c,index(b,,1)=z)))}))))