addressing duplicated rows including a select statement within a inner join in sql closed queries

You are really close, but need one extra step.

In the joined sub-query, you actually need to name the sub-query itself which creates a virtual table with that name. Then link that virtual table as normal.

e.g., in the below I named the sub-query ‘dzn_subquery’ and changed the joins on dzn to dzn_subquery (all changes are on the bottom line).

inner join (
select
dzn.acct_nbr_id,
dzn.order_no,
t.act_open_date
from 
tb_dzn_order_scd dzn
GROUP BY dzn.acct_id , dzn.order_nbr
-- changed following line
) AS dzn_subquery on a.acct_id = dzn_subquery.acct_nbr_id and  a.order_no= dzn_subquery.order_no